Find last row from Access VBA (1 Viewer)

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:39
Joined
Aug 30, 2003
Messages
36,118
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

Smeghead
Local time
Today, 02:39
Joined
Jan 12, 2001
Messages
32,059
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

Registered User.
Local time
Today, 04:39
Joined
Mar 25, 2002
Messages
516
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
 
Last edited:

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:39
Joined
Aug 30, 2003
Messages
36,118
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

Registered User.
Local time
Today, 09:39
Joined
Apr 21, 2002
Messages
91
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

Wino Moderator
Staff member
Local time
Today, 02:39
Joined
Aug 30, 2003
Messages
36,118
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

Registered User.
Local time
Today, 09:39
Joined
Apr 21, 2002
Messages
91
i know what you mean, its old skool but i still prefer it to lastcell range pasting which lacks poetry in my opinion.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:39
Joined
Aug 30, 2003
Messages
36,118
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. :D
 

Users who are viewing this thread

Top Bottom