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

Question

I found a similar solution to this question in c# How to Select all the cells in a worksheet in Excel.Range object of c#?

What is the process to do this in VBA?

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") 

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?

Answer 1

I believe you want to find the current region of A1 and surrounding cells - not necessarily all cells on the sheet. If so - simply use... Range("A1").CurrentRegion

Answer 2

You can simply use cells.select to select all cells in the worksheet. You can get a valid address by saying Range(Cells.Address).

If you want to find the last Used Range where you have made some formatting change or entered a value in you can call ActiveSheet.UsedRange and select it from there. Hope that helps