Excel – Declare a Range relative to the Active Cell with VBA

excelvba

I need to declare a range object relative to the Active Cell. The problem is, the number of rows and columns I want to select is different each time the macro runs.

For example, I have two variables: numRows and numCols.

I want to select a range that has the ActiveCell in the upper-left corner hand has the cell with row ActiveCell.Row + NumRows and column ActiveCell.Column + NumCols in the bottom right (and then I intend to copy this data to an array to speed up my macro).

Any suggestions on how to do this?

Best Answer

There is an .Offset property on a Range class which allows you to do just what you need

ActiveCell.Offset(numRows, numCols)

follow up on a comment:

Dim newRange as Range
Set newRange = Range(ActiveCell, ActiveCell.Offset(numRows, numCols))

and you can verify by MsgBox newRange.Address

and here's how to assign this range to an array

Related Solutions

Excel – Return a range from A1 to the true last used cell

You need to make these mods to the code

The sheet may be blank, so you should never use Select with Find as this will give an error if the Find returns nothing. Instead test that the range object Is Not Nothing
Find can search by row and by column. You need to determine both last row and column to determine the true last used cell
Once you have determined the true last cell use Range to set a range from the first cell (A1) to your cell determined with the two Find ranges

Pls see the code below

If the Find gets a value then it makes a range rng3 from A1 to the last used cell identified by the two Finds.

enter image description here

Sub GetRange()
    Dim rng1 As Range
    Dim rng2 As Range
    Dim rng3 As Range
    Set rng1 = Cells.Find("*", [a1], xlFormulas, , xlByRows, xlPrevious)
    Set rng2 = Cells.Find("*", [a1], xlFormulas, , xlByColumns, xlPrevious)
    If Not rng1 Is Nothing Then
        Set rng3 = Range([a1], Cells(rng1.Row, rng2.Column))
        MsgBox "Range is " & rng3.Address(0, 0)
        'if you need to actual select the range (which is rare in VBA)
        Application.Goto rng3 
    Else
        MsgBox "sheet is blank", vbCritical
    End If
End Sub

Best Answer

Related Solutions

Excel – Return a range from A1 to the true last used cell

Related Topic