Retrieve Range Values

One of the most common situations where VBA is utilized is in the manipulation of Excel worksheets.

Retrieving the value of a specific cell or range is fundamental to data processing in Excel.

For example, when dealing with complex conditions that cannot be resolved with functions alone, it is effective to use VBA to retrieve data and process it accordingly.

Additionally, when obtaining data from sources other than Excel and processing it in Excel, VBA can be used to retrieve the data.

This page explains how to retrieve values from a specific range in a worksheet using VBA.

How to Retrieve Range Values

To retrieve the value of a specific cell or range, use the Range property of the Worksheet object.

Dim ws As Worksheet

' Retrieve the worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")

' Retrieve the value of cell A1
Dim cellValue As Variant
cellValue = ws.Range("A1").Value

' Retrieve the values of the range A1:C10
Dim rangeValues As Variant
rangeValues = ws.Range("A1:C10").Value

Specify the address as a string in the argument of the Range property, similar to how you would specify it in a function.

You can also use the Cells property of the Worksheet class to specify the start and end cells of the range.

' Retrieve the values of the range from cell A1 to cell C10
Dim rangeValues As Variant
rangeValues = ws.Range(ws.Cells(1, 1), ws.Cells(10, 3)).Value

Specific Examples of Retrieving a Range

Retrieving Data from a Specific Range

The following code is an example of retrieving data from a specified range in a specific sheet of the opened workbook as an array.

Sub GetRangeValues()
    Dim ws As Worksheet
    Dim targetRange As Range
    Dim data As Variant

    ' Retrieve the worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")

    ' Specify the specific range
    Set targetRange = ws.Range("A1:C10")

    ' Retrieve the data of the range as an array
    data = targetRange.Value

    ' Process the array data (here, simply output it)
    Dim i As Long, j As Long
    For i = LBound(data, 1) To UBound(data, 1)
        For j = LBound(data, 2) To UBound(data, 2)
            Debug.Print data(i, j)
        Next j
    Next i
End Sub

When this code is executed, the data in the range A1:C10 of the SalesData sheet is retrieved as an array and output to the debug window in the order of A1, A2, A3, …

Processing Based on the Retrieved Range Data

You can further process the retrieved data. For example, you can filter the data based on specific conditions and output it to another sheet.

Sub FilterAndOutputData()
    ' ... (continue from the previous code)

    ' Filter the data and output it to a new sheet
    Dim outputWs As Worksheet
    Set outputWs = wb.Worksheets.Add
    outputWs.Name = "FilteredData"

    Dim outputRow As Long
    outputRow = 1

    ' Output only the data that meets the condition
    For i = 1 To UBound(data, 1)
        If data(i, 3) > 1000 Then ' If the value in the 3rd column exceeds 1000
            For j = 1 To UBound(data, 2)
                outputWs.Cells(outputRow, j).Value = data(i, j)
            Next j
            outputRow = outputRow + 1
        End If
    Next i
End Sub

This code outputs only the rows where the value in the 3rd column exceeds 1000 to a new sheet.

Summary

As mentioned at the beginning, retrieving values from a specific range in a worksheet using VBA is fundamental to data processing in Excel.

Among the methods, the one using the Range property explained here is particularly frequently used.

Please use this as a basis to create VBA code tailored to your work.