Vba

[IanT]

Hi

I need to be able to select the last cell in a column of data and use the value in a vba function, can any help with this! The column of data will change as I run a report.

Thanks.

 

[chergh]

If the position of the column is going to remain constant, and the number of rows is going to vary, you can do it like this

sub lastcellincol()

dim x as integer
dim lastrow as long
dim ws as worksheet
dim wb as workbook

set wb = workbooks("your_workbook_name")
set ws = wb.worksheets("worksheet_name")

lastrow = ws.range("A1").End(xldown).row

x = ws.range("A" & lastrow)

end sub

So here x is the variable you will use in your function.

 

[shades]

Just a note. It is better (and more efficient) to not use Integer but as Long. Excel actually converts Integer to Long anyway. And if you have more than 32,000 rows, Integer will not suffice.
________
BUY AROMED VAPORIZER

 

[boblarson]

Just a note. It is better (and more efficient) to not use Integer but as Long. Excel actually converts Integer to Long anyway. And if you have more than 32,000 rows, Integer will not suffice.

And if you move into Excel 2007 you can potentially have over a million rows.