Help addressing the last cell

[troy_lee]

Here is my code:

Dim LastRow As Long
Dim LastCol As Long
Dim LastCell As Range
Dim intNumCols As Long
Dim DataCells As Range

'Find the last row, column and last cell of the worksheet.
LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
LastCell = ActiveSheet.Cells(LastRow, LastCol).Address

Excel fails at the LastCell line. It tells me that the object is not
defined, but clearly my variable is defined. Both LastRow and LastCol
return the correct value.

What is the problem?

Thanks in advance.

 

[Ron Rosenfeld]

Here is my code:

Dim LastRow As Long
Dim LastCol As Long
Dim LastCell As Range
Dim intNumCols As Long
Dim DataCells As Range

'Find the last row, column and last cell of the worksheet.
LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
LastCell = ActiveSheet.Cells(LastRow, LastCol).Address

Excel fails at the LastCell line. It tells me that the object is not
defined, but clearly my variable is defined. Both LastRow and LastCol
return the correct value.

What is the problem?

Thanks in advance.

You are trying to set a range object to be equal to a string.

LastCell is a Range

But ....Address is a string.

If you want to set LastCell = to the range object represented by the last cell
in your worksheet, then you should:


Set LastCell = ActiveSheet.Cells(LastRow, LastCol)

If you really want to set LastCell = to the Address, which is a string, then
you should

Dim LastCell as String

I'm not sure exactly what you are trying to do, but be aware that your code
will give you the last column used in Row 1, and the last row used in Column A.

So if you have a worksheet where your only entry is in F10, your code will
return A1.
--ron