PDA

View Full Version : Solved: Find the last cell in my spreadsheet.



Djblois
08-29-2006, 01:21 PM
This is what I have so far:

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)

it is giving me an error on the finalCell line.

Zack Barresse
08-29-2006, 01:27 PM
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
08-29-2006, 01:32 PM
Hi again,

When assigning a cell to a range variable, you need to use the word Set as the type Range is an object:Set finalCell = Range(totalrow, lastColumn)Also, you'll need to make a slight change to your dim statements:'Dim totalrow, lastColumn As Long 'cant do it this way, need to specify each
Dim totalrow As Long, lastColumn As Long
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
08-29-2006, 01:35 PM
LOL! I'll stop doing that now Matt.

mvidas
08-29-2006, 01:42 PM
Whats that, posting before me?

Zack Barresse
08-29-2006, 02:54 PM
Yeah, same solutions.