Find and select all cell ranges that are not empty-CodePudding

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