Thread: Help with variables in "Range"

Do you really need to select the data? When working with macros it's rarely a requirement.

Are all of the rows going to have the same number of columns in them, or might it vary. If it's always the same then try:

Sub Example()

Const lWORKING_COLUMN=2

Dim rngWorkingArea as Range

With Sheets("Generic Sheet Name")

  Set rngWorkingArea=.Cells(.Rows.Count,lWORKING_COLUMN).End(xlUp)
  Set rngWorkingArea=rngTempRange.End(xlUp)
  Set rngWorkingArea=.Range(rngWorkingArea, rngWorkingArea.Offset(0,.Columns.Count-(lWORKING_COLUMN+1)).End(xlToLeft))

End With

End Sub

At the end of that the working area will contain the last row, from column B across to the last used cell in the row and you can then do anything with that range that you'd normally do with a selection.

Thank you Andrew for your reply! And sorry it took all night for me to get back.

I have changed my code like your example, and now it works to do what I need it to!

Sub Sample()

    Dim lastRow As Range, refCell As Range
    
    Sheets("Generic Sheet").Select
    Set lastRow = Range("B1048576").End(xlUp)
    Set refCell = lastRow.End(xlUp)
    Set refCell = Range(refCell, refCell.Offset(0, Columns.Count - 3).End(xlToLeft))
    refCell.Select

End Sub

Do you really need to select the data? When working with macros it's rarely a requirement.

No, I really didn't need to, but it's just my way of making sure I have the macro looking at the right stuff, if you will.
I just started learning VBA about a week ago, so I probably do some things the long way because I don't exactly know all that's available to me yet. I'm ready to learn more though!

Thanks again for the quick reply!