Solved: Find the last cell in my spreadsheet.

[Djblois]

This is what I have so far:

[VBA]Dim totalrow, lastColumn As Long
Dim finalCell As Range

totalrow = Cells(65536, 1).End(xlUp).Offset(1, 0).Row
lastColumn = Cells(1, 100).End(xlToLeft).Column
finalCell = Range(totalrow, lastColumn)[/VBA]

it is giving me an error on the finalCell line.

[Zack Barresse]

Hey,

Take a look at these two KB entries:

http://vbaexpress.com/kb/getarticle.php?kb_id=417
http://vbaexpress.com/kb/getarticle.php?kb_id=418

HTH

[mvidas]

Hi again,

When assigning a cell to a range variable, you need to use the word Set as the type Range is an object:[vba]Set finalCell = Range(totalrow, lastColumn)[/vba]Also, you'll need to make a slight change to your dim statements:[vba]'Dim totalrow, lastColumn As Long 'cant do it this way, need to specify each
Dim totalrow As Long, lastColumn As Long[/vba]
Some other things to keep in mind:
-Your uses of End will only work if something is in row 1 for the last column, and something is in column A for the last row, otherwise you'll miss some data
-Instead of using 65536, you might want to use Rows.Count as the next version of excel will allow over a million rows.
-Similarly, instead of using 100 for the column, you could use Columns.Count (256 currently, many many more for the next version)
You could also take a look at http://vbaexpress.com/kb/getarticle.php?kb_id=417 and http://vbaexpress.com/kb/getarticle.php?kb_id=418 to see how to use the Find method of determining the last used row/column, makes things much more useful for sheets without a specific setup.
Matt

[Zack Barresse]

LOL! I'll stop doing that now Matt.

[mvidas]

Whats that, posting before me?

[Zack Barresse]

Yeah, same solutions.