Select Range Until First Empty Row And Column

[dirk1965]

I'm looking for some assistance in being able to automatically select a range based on when the first empty row and first empty column are detected. I've read up on Range.End, but that seems like its detecting for the very last row/column which doesn't account for an empty row or column in the middle of a data set. I would like to detect the very first fully empty row and column.

Thanks

 

[Tetra201]

Try using these:

LastCol = Columns(ActiveSheet.UsedRange.Column + ActiveSheet.UsedRange.Columns.Count - 1).Column
LastRow = Rows(ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1).Row

 

[dirk1965]

Does that get put into a macro VBA?

 

[Tetra201]

Yes.
Oops -- I misread your OP. Please disregard those formulas. I will try to come up with different ones.

 

[JLGWhiz]

If you want VBA code. There are several approaches to defining the limits of your data body. Generally you can use the following code:
For last column with data or formula.

Dim lastCol As Long
lastCol = ActiveSheet.Cells.Find("*", , xlFormulas, xlPart, xlByColumns, xlPrevious).Column

For last row with data or formula.

Dim lastRow As Long
lastRow = ActiveSheet.Cells.Fing("*", , xlFormulast, xlPart, xlByRows, xlPrevious).Row

You then use these two variables in the code as a numerical representation of a column as in the following examples:
Cells(lastRow, lastCol) returns cell address for the lower right extreme of the UsedRange.
Range("A" & lastRow) returns the last cell in column A that contains data or a formula.
Columns(lastCol) returns the column number for the last column containing data or a formula.
Rows(lastRow) returns the row number for the last row containing data or a formula.
The code above gets the column or row number by starting at the end of the column or row and working back to the first cell encountered that has data or a formula which the (*) wild card will detect.

If you want to select from cell E5 to the last column and last row then

ActiveSheet.Range("E6", ActiveSheet.Cells(lastRow, lastCol).Select

 

[dirk1965]

I'm not actually looking for the 'last' row or column of data. I looking for the first empty row and first empty column. There could be data in other row/columns after the first empty row/column, but I don't want to include those in my range.

 

[Tetra201]

The following VBA code will give you the first emty row (eRow) and first empty column (eCol):

eRow = 1
Do While Application.CountA(Rows(eRow).EntireRow) > 0
    eRow = eRow + 1
Loop
eCol = 1
Do While Application.CountA(Columns(eCol).EntireColumn) > 0
    eCol = eCol + 1
Loop

 

[dirk1965]

The following VBA code will give you the first emty row (eRow) and first empty column (eCol):

eRow = 1
Do While Application.CountA(Rows(eRow).EntireRow) > 0
    eRow = eRow + 1
Loop
eCol = 1
Do While Application.CountA(Columns(eCol).EntireColumn) > 0
    eCol = eCol + 1
Loop

Thanks for the assist. I'll give it a try.

 

[dirk1965]

So I tried something simple. However, I'm getting a compile error with my range statement. Any idea what might be wrong?

Sub Range()

eRow = 1
Do While Application.CountA(Rows(eRow).EntireRow) > 0
eRow = eRow + 1
Loop
eCol = 1
Do While Application.CountA(Columns(eCol).EntireColumn) > 0
eCol = eCol + 1
Loop

Range(Cells(1, 1), Cells((eRow - 1), (eCol - 1))).Interior.Color = rgbLightBlue

End Sub

 

[Tetra201]

So I tried something simple. However, I'm getting a compile error with my range statement. Any idea what might be wrong?

Sub Range()

Just change the name of your sub to, for example, BlueRange. "Range" is a reserved vord in VBA.