How do I get a Macro to find the end of a range of data?

Question

I'm using XL2003, I need to write a macro/vbs program which will select all the data in a table up to the end one column within the table. The end of the column will move around depending on other factors, so the macro must look for the end of the data and select up to that point.

I'm quite new to Macros but otherwise quite competent at Excel so please give as much detail as you like.

Thanks very much!

Answer 1

The best way is to use the built-in Excel functions in VBA, like this
Worksheetfunction. COUNTA( "Sheet1!1:1")
Or
Worksheetfunction. COUNTA( Worksgeets("Sheet1"). range("1:1").range)
This object Worksheetfunction will enables you (When you are in VBA) to access most of the Excel functions (Most of, not all, you can not use OFFSET and alike functions here)

So, you said you are good in Excel, then you can do the rest.
If not, mail me here in Y! Answers for it

I told you, I am the VBAXLMan

Answer 2

Record the macro manually and edit it. Its the best way to learn.

Start recording the macro
Select the cell at the top of the column
Press ctrl and the Down Arrow (takes you to the end of the list)
Stop recording the macro.
Edit it to do what you want.

There are ways to put select relative cells from this cell (eg 2 columns left), but I cant remember them, again record a macro is the best way and use the arrow keys to select the cells does that I think.

Answer 3

Easiest way is to do it by hand and then edit the result you have recorded. I can't help feeling you're trying to do something that's not quite parctical in an automatic environment.

Answer 4

Use Ctrl End

Answer 5

a formula will do that....
Assume you have a column of data D6:D32, which may or may not be filled to the bottom
D6 is the top of the column
=HLOOKUP(D6,D6:D32,COUNTA(D6:D32)))
will return the data in the last populated cell in your column

Answer 6

range(activecell, activecell.end(xldown)).select