How do you select the entire excel sheet with Range using Macro in VBA?

Question posted 2013 · +14 upvotes

I found a similar solution to this question in c#… See link below

How to Select the whole excel sheet in Excel.Range object of c#?

Does anyone have a snippet to do this in VBA? I’m not really familiar with VBA, so this would be helpful. Here’s what I’ve got so far…

I select data normally by using “ctrl+shift over arrow, down arrow” to select an entire range of cells. When I run this in a macro it codes out A1:Q398247930, for example. I need it to just be

.SetRange Range("A1:whenever I run out of rows and columns")

This is very simple, and I could easily do it myself without a macro, but i’m trying to make the entire process a macro, and this is just a piece of it.

Sub sort()
'sort Macro
Range("B2").Select
ActiveWorkbook.Worksheets("Master").sort.SortFields.Clear
ActiveWorkbook.Worksheets("Master").sort.SortFields.Add Key:=Range("B2"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Master").sort
.SetRange Range("A1:whenever I run out of rows and columns")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

edit: There are other parts where I might want to use the same code but the range is say “C3:End of rows & columns”. Is there a way in VBA to get the location of the last cell in the document?

Thanks!