Dynamic Range in VBA offest issues-CodePudding

tried many examples and followed many videos but no luck please help I am trying to create a dynamic range starting at E2

try 1

Set StatusCol = Sheet1.Range("E2:E10")

try 2

Set StatusCol = OFFSET(Sheet1!$E$2,0,0,COUNTA(Sheet1!E2:E500),1)

CodePudding user response：

Try this

set statuscol = Sheet1.Range("E2:E" & Sheet1.cells(Rows.count,1).End(xlUp).row)

You can then run statuscol.select to make sure your desired range is selected

Note: the 1 assumes you have data in every row of column 1 (A). change it to a different column number if that is not the case.

CodePudding user response：

If you want to use built in excel function to vba then have to use Application.WorkSheetFunction method. Otherwise can try below sub.

Sub TillLastCol()
Dim StatusCol As Range
Dim sh As Worksheet
Dim lr As Long

    Set sh = Sheets("Sheet1")
    lr = sh.Cells(sh.Rows.Count, "E").End(xlUp).Row
    
    Set StatusCol = sh.Range("E2:E" & lr)
    'Debug.Print StatusCol.Address
End Sub