Home > Enterprise >  Find and select all cell ranges that are not empty
Find and select all cell ranges that are not empty

Time:06-18

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.

enter image description here

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
  • Related