Get Last Row errors in empty column

[abenitez77]

I am using this sub to get the last row and column with data. If it tries to search a column that is empty, it errors out. How can i avoid this ? or do this better?

Public Sub GetLastRow(RowLast As Long, ColLast As Long) Dim lastRow As Range, ws As Worksheet Dim lastColumn As Range

Application.EnableEvents = False
Application.ScreenUpdating = False
pLastRow = 0
pLastCol = 0

Set ws = ActiveSheet

'Get Last Row with Data in Worksheet using SpecialCells
'Debug.Print ws.Cells.SpecialCells(xlCellTypeLastCell).Row
'Set lastRow = ws.Cells.SpecialCells(xlCellTypeLastCell).EntireRow
Set lastColumn = ws.Cells.SpecialCells(xlCellTypeLastCell).EntireColumn
RowLast = Columns(4).Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious, After:=Cells(18000, 4)).Row
'RowLast = lastRow.Row
ColLast = lastColumn.Column
 ' pLastRow, pLastCol

 If RowLast < 1 Then
    pLastRow = 1
 Else
    pLastRow = RowLast
 End If

 If ColLast < 1 Then
    pLastCol = 1
 Else
    pLastCol = ColLast
 End If
Quit: Application.ScreenUpdating = True Application.EnableEvents = True On Error GoTo -1: On Error GoTo 0 
End Sub

 

[JLGWhiz]

You would need to either use an If...Then statement to qualify the range you are interrogating for a string, or use a diffenent syntax to get the last row like"

RowLast = ws.Cells(Rows.Count, 4).End(xlUp).Row

The Find syntax is more useful with evauating the last row for multiple columns.

 

[Rick Rothstein]

These two code lines will return the last row number and last column number that contains data...

LastUsedRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row

LastUsedCol = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column

 

[JLGWhiz]

These two code lines will return the last row number and last column number that contains data...

LastUsedRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row

LastUsedCol = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column

The OP problem was getting the Obect Variable not Set because the column is blank. These only work if there is data or formulas.

 

[Rick Rothstein]

The OP problem was getting the Obect Variable not Set because the column is blank.

Sorry, but I do not understand this. The OP's opening line in the first message was "I am using this sub to get the last row and column with data." Are you saying that is not what he actually wants to do?

 

[JLGWhiz]

Sorry, but I do not understand this. The OP's opening line in the first message was "I am using this sub to get the last row and column with data." Are you saying that is not what he actually wants to do?

I was going by this statement.

If it tries to search a column that is empty, it errors out. How can i avoid this ?

In the code that was in the OP, the Find method was being used but only for a single column. So I assumed that the error was based on a single column query.

 

[abenitez77]

These two code lines will return the last row number and last column number that contains data...

LastUsedRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row

LastUsedCol = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column

This is what I needed. The last row and last column where always different. When the sheet had an empty column it was searching on, it would error out on. Your solution works.

Thanks!