Finding the end of data

[lous]

My apologies if this has been asked before, but I'm having a little trouble with an automation task that I am doing.

I have some reports that are in several different workbooks and have them automatically combined into a different workbook, summarizing different things using macros.

My question is -- in some of the workbooks, there is a total at the bottom of how many of this or how many of that. But my problem is that the report isn't the same length every day... so I can't just tell Excel to pick cell "D283" and copy it.

What I'd like it to do is find out where the end of the data is by seeing that there's two blank cells just above the total I want it to pick up. Then paste it into a different workbook.

Is there a way to do this??

Any help would be appreciated.

 

[RobBroekhuis]

If the total is always at the bottom of the columns, then it's easy. Say your data is in column B, and your header row is row 3:

set MyTotal = cells(65536,2).end(xlup)

this sets the range variable MyTotal to the cell you're looking for. Now you can do things like

workbooks("Summary&quot.sheets(1).range("C3&quot = MyTotal.value

(or whatever else you feel like)



Rob

 

[ibbguy]

If you are simply looking for the last used row, you use the folowing script:

activesheet.usedrange.rows.count


Bob

 

[xlbo]

Just a little pointer here to all those that use USEDRANGE.ROWS.COUNT

This works fine IF the data starts in Row 1 BUT if it doesn't, the row count will NOT equal the last row of data
That's why I always use Range("A65536&quot.end(xlup).row
which will ALWAYS tell you whee the last row of data is (unless your columns are different lengths ;-) )

check the FAQs on this forum - there are at least 2 that deal with this Rgds
~Geoff~

 

[Loomah]

UsedRange is dodgy. Good for a quck reference but that's it as far a reliability is concerned as it could contain blank cells that were used once upon a time. That's my opinion anyway!!

Though you could use this line

ActiveSheet.UsedRange.End(xlDown).Row

as it don't care where ya data starts

xlbo
shouldn't that be "excellent" FAQs??!!!??

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[xlbo]

Loomah - Good point but no good for non-contiguous ranges and yes they are "excellent" FAQs - both mine AND yours ;-)
Rock On ! Rgds
~Geoff~

 

[Loomah]

This is good for non contig tho

.UsedRange.SpecialCells(xlLastCell).Row

Anyway, this discussion has been had a million times before and there are always many answers. The glory of tek-tips! In this case I'm still using usedrange so reliability still isn't guarenteed!

Bliss
;-) If a man says something and there are no women there to hear him, is he still wrong?