Macro to select a range using last row and column?

[tonywatsonhelp]

Hi Everyone,

I just need a macro to select the following range for me

Sheet(Data1).Range A6: Last row (calculated from column a) and Last Column (Calculated from Row 6

Please help if you can

Thanks

Tony

 

[Marcelo Branco]

Maybe...

Dim lr As Long, lc As Long
Dim myRange As Range

With Sheets("Data1")
    'Get last row
    lr = .Cells(.Rows.Count, "A").End(xlUp).Row
    'Get last column
    lc = .Cells(6, .Columns.Count).End(xlToLeft).Column
    'Set data range
    Set myRange = Range(.Cells(6, "A"), .Cells(lr, lc))
End With

M.

 

[Fluff]

Beaten 2it

 

[hiker95]

tonywatsonhelp,

Try the following macro.

Sub tonywatsonhelp()
' hiker95, 04/24/2018, ME1052941
Dim lr As Long, lc As Long
With Sheets("Data1")
  lr = .Range("A6").CurrentRegion.Rows.Count
  lc = .Cells(6, Columns.Count).End(xlToLeft).Column
  .Range("A6").Resize(lr, lc).Select
End With
End Sub

 

[tonywatsonhelp]

THank you
Marcelo, this is great,
Fluff always appreciate your help so thanks
and Hiker great alternative thank you

Tony

 

[hiker95]

tonywatsonhelp,

Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.