I have a large range of cells that I am trying to select easily because it is too large to manually select and copy every time. I am looking for a way to quickly locate the entire range of data and select it. Not to be confused with only selecting every non-empty cell. There are cells within the big range that could be empty, but I also want to include them.
Example is my photo. I want to discover and select all the data that is A1:E7, including the cells in the middle that may be empty. If possible, I would want to de-select the heading as well and only select A2:E7.
So far I have managed to select everything with:
Range("A1").CurrentRegion.Select
But I am unsure how to de-select the top row after that.
CodePudding user response:
Try this
Sub selectWithoutHeaders()
With Range("A1").CurrentRegion
.Offset(1, 0).Resize(.Rows.Count - 1).Select
End With
End Sub
CodePudding user response:
You can use the handy UsedRange
to determine the filled area on the sheet without checking each row and column for their last cell coordinates.
With UsedRange, you create a range stretching from A2 to the last cell, and then select it.
Sub Example()
Dim WS As Worksheet
Set WS = ActiveSheet
Dim LastCell As Range
With WS.UsedRange
Set LastCell = .Cells(.Rows.Count, .Columns.Count)
End With
WS.Range("A2", LastCell).Select
End Sub
CodePudding user response:
Here is the Range(Cells(2, 1), Cells(LastRow, LastColumn)).Select
version.
Public Function GetLastRowOfSheet(ws As Worksheet) As Long
Dim usedRng As Range
Dim lastRow As Range
Set usedRng = ws.UsedRange
Set lastRow = usedRng.Rows(usedRng.Rows.Count).EntireRow
GetLastRowOfSheet = lastRow.row
End Function
Public Function GetLastColOfSheet(ws As Worksheet) As Long
Dim usedRng As Range
Dim lastRow As Range
Set usedRng = ws.UsedRange
Set lastCol = usedRng.Columns(usedRng.Columns.Count).EntireColumn
GetLastColOfSheet = lastCol.Column
End Function
Sub SelectAllCells()
Dim ws As Worksheet
Set ws = ActiveSheet
Dim lastRow As Long: lastRow = GetLastRowOfSheet(ws)
Dim lastCol As Long: lastCol = GetLastColOfSheet(ws)
Range(Cells(2, 1), Cells(lastRow, lastCol)).Select
End Sub