Find last row from Access VBA

[pbaldy]

I'm working with a series of Excel files from Access. I need to add a "total" column to an imported spreadsheet with no set number of rows. I know how to add a formula to the new column, but how to copy it down without knowing the last row?

 

[boblarson]

Paul:

I usually go in and record a macro to do what I want it to do and then look at the result and modify it if neccessary. I got this when I did it:

ActiveCell.SpecialCells(xlLastCell).Select

 

[shades]

One caution: you want to find out whether that gives the last cell based on top-down search or bottom-up search. They do not necessarily return the true "last cell".
________
HERBAL VAPORIZERS

 

[pbaldy]

Thanks Bob. I actually do the same thing, but missed the last cell bit (forgot about end-home). Unfortunately, it wouldn't work with late binding, which I'm using. I got a compile error on xlLastCell. I ran it in Excel to get the constant, and now it works.

Shades, I appreciate the clarification. I've got it running now and it seems to select what I want it to.

 

[clive2002]

Presumming you have a column which is always poplated you can run something like the below.

i represent the row number which is looped through till it hits a blank, you could always change the blank to 'total' or something like that depending on your data.

The example looped just adds the 2nd and 3rd column and puts the value in the forth, obvoiusly you can adapt this to your data, just a quick example.

~#################

Sub Process()
dim i as long

i = 2
Do Until Cells(i, 1) = ""
Cells(i, 4).Value = Cells(i, 2).Value + Cells(i, 3).Value
i = i + 1
Loop

End sub

 

[pbaldy]

Thanks Clive. I had actually already contemplated something like that, but I thought of it as a "brute force" approach. I figured there must be a built-in way, and there was.

 

[clive2002]

i know what you mean, its old skool but i still prefer it to lastcell range pasting which lacks poetry in my opinion.

 

[pbaldy]

And if I've got a 10,000 row spreadsheet, I think it's more efficient to copy something to all of them at once rather than perform an operation on each row individually. Personal preference I guess. At the end of the day, we each must use what we feel comfortable with. That's why there are always 10 different ways to accomplish a goal.