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?
This question is related to
excel
vba
excel-2010
you can use all cells as a object like this :
Dim x as Range
Set x = Worksheets("Sheet name").Cells
X is now a range object that contains the entire worksheet
Refering to the very first question, I am looking into the same. The result I get, recording a macro, is, starting by selecting cell A76:
Sub find_last_row()
Range("A76").Select
Range(Selection, Selection.End(xlDown)).Select
End Sub
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
Sub SelectAllCellsInSheet(SheetName As String)
lastCol = Sheets(SheetName).Range("a1").End(xlToRight).Column
Lastrow = Sheets(SheetName).Cells(1, 1).End(xlDown).Row
Sheets(SheetName).Range("A1", Sheets(SheetName).Cells(Lastrow, lastCol)).Select
End Sub
To use with ActiveSheet:
Call SelectAllCellsInSheet(ActiveSheet.Name)
I have found that the Worksheet ".UsedRange" method is superior in many instances to solve this problem. I struggled with a truncation issue that is a normal behaviour of the ".CurrentRegion" method. Using [ Worksheets("Sheet1").Range("A1").CurrentRegion ] does not yield the results I desired when the worksheet consists of one column with blanks in the rows (and the blanks are wanted). In this case, the ".CurrentRegion" will truncate at the first record. I implemented a work around but recently found an even better one; see code below that allows copying the whole set to another sheet or to identify the actual address (or just rows and columns):
Sub mytest_GetAllUsedCells_in_Worksheet()
Dim myRange
Set myRange = Worksheets("Sheet1").UsedRange
'Alternative code: set myRange = activesheet.UsedRange
'use msgbox or debug.print to show the address range and counts
MsgBox myRange.Address
MsgBox myRange.Columns.Count
MsgBox myRange.Rows.Count
'Copy the Range of data to another sheet
'Note: contains all the cells with that are non-empty
myRange.Copy (Worksheets("Sheet2").Range("A1"))
'Note: transfers all cells starting at "A1" location.
' You can transfer to another area of the 2nd sheet
' by using an alternate starting location like "C5".
End Sub
I would recommend recording a macro, like found in this post;
Excel VBA macro to filter records
But if you are looking to find the end of your data and not the end of the workbook necessary, if there are not empty cells between the beginning and end of your data, I often use something like this;
R = 1
Do While Not IsEmpty(Sheets("Sheet1").Cells(R, 1))
R = R + 1
Loop
Range("A5:A" & R).Select 'This will give you a specific selection
You are left with R = to the number of the row after your data ends. This could be used for the column as well, and then you could use something like Cells(C , R).Select, if you made C the column representation.
you have a few options here:
I personally use the Used Range and find last row and column method most of the time.
Here's how you would do it using the UsedRange property:
Sheets("Sheet_Name").UsedRange.Select
This statement will select all used ranges in the worksheet, note that sometimes this doesn't work very well when you delete columns and rows.
The alternative is to find the very last cell used in the worksheet
Dim rngTemp As Range
Set rngTemp = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
If Not rngTemp Is Nothing Then
Range(Cells(1, 1), rngTemp).Select
End If
What this code is doing:
Here is what I used, I know it could use some perfecting, but I think it will help others...
''STYLING''
Dim sheet As Range
' Find Number of rows used
Dim Final As Variant
Final = Range("A1").End(xlDown).Row
' Find Last Column
Dim lCol As Long
lCol = Cells(1, Columns.Count).End(xlToLeft).Column
Set sheet = ActiveWorkbook.ActiveSheet.Range("A" & Final & "", Cells(1, lCol ))
With sheet
.Interior.ColorIndex = 1
End With
Maybe this might work:
Sh.Range("A1", Sh.Range("A" & Rows.Count).End(xlUp))
Another way to select all cells within a range, as long as the data is contiguous, is to use Range("A1", Range("A1").End(xlDown).End(xlToRight)).Select
.
Source: Stackoverflow.com