Excel Select Range even if the bottom right cell is empty

Question

I am trying to select a range in my Excel Sheet but I just can't figure out how. The data is in a way that in Row 2 all cells are filled to the end and in Column A all cells are filled to the bottom. But within the table there can be empty cells. So the bottom right corner can be empty as well. I'd like the range to start at A3.

I have tried to select the range in this way:

Range("A3",Range("ZZ2").End(xlToLeft).Range("A1000000").End(xlUp)).Select

or this way:

Range("A3",Range("A1000000").End(xlUp).Range("ZZ2").End(xlToLeft).).Select

But either way the whole table never gets selected because there is nothing in the bottom right cell. I feel like there must be a easy solution to this but I just can't see it.

This is a simple example of what the table could look like. The range I want to select is the green area.

Indeed there is an easy solution but the explanation isn't very clear. An image would help a lot in this case. — ashleedawg

FunThomas FunThomas · Accepted Answer · 2018-06-12T07:49:40

Look at this piece of code. It will figure out last column and last row separately - assuming that you have at least one row and one column that is filled out completely. Looking at your example data, I have taken your header row (=2) and the first column (=A)

Dim r As Range, row As Long, col As Long
With ActiveSheet
    ' Look in Col A (=1) to figure out number of rows
    row = .Cells(.Rows.Count, 1).End(xlUp).row 
    ' Look in Row 2 to figure out number of columns
    col = .Cells(2, .Columns.Count).End(xlToLeft).Column
    Set r = .Range(.Cells(3, 1), .Cells(row, col))
End With
Debug.Print r.Address

Excel Select Range even if the bottom right cell is empty

2 Answers