Find Last Data In Columns

[nehpets12]

I have 8 columns B to I in these I have either 1 or 0.5 I need a macro that that goes down to the last entry in any of these columns and selects the entry in I.

I only need to find this data in columns B C G H I

I am setting it up to create a print area A1 to the active cell I hope this is clear.

[AJ]

I think there are quite a few ways of doing this.
One way of getting the last value in Column I would be:

MyValue = Range("I65536").End(xlUp).Value

Alternatively you could mess about with the SpecialCells feature:

LastCol = ActiveSheet.Cells.SpecialCells(xlLastCell).Column
LastRow = ActiveSheet.Cells.SpecialCells(xlLastCell).Row

Specialcells is the equivalent of presing Ctrl+End - it takes you to the last used cell on your sheet.

These help?

Rgds
AJ

[Cosmos75]

On 2002-04-15 10:07, nehpets12 wrote:
I have 8 columns B to I in these I have either 1 or 0.5 I need a macro that that goes down to the last entry in any of these columns and selects the entry in I.

I only need to find this data in columns B C G H I

I am setting it up to create a print area A1 to the active cell I hope this is clear.

If you you last cell with data in a column was in Column D32 and In Column I it's I10,

This will bring you to I32.

Range("B1").Select
ActiveCell.SpecialCells(xlLastCell).Select

But what if I32 is empty? Or are you only using this to set the print area?

Edit: AJ beat me to it by 2 minutes!

[ This Message was edited by: Cosmos75 on 2002-04-15 10:17 ]

[Al Chara]

Those codes will not work if there is data in columns past I.

nehpets12: Does your data stop at column I?

[Cosmos75]

Al Chara,

GOOD POINT! Didn't think to ask that!!

[nehpets12]

in columns "d" "e" "f" I have data that goes to the end of the sheet I only need to find the last data in columns "b" "c" "g" "h" "i" and select the active cell in coumn I

[Cosmos75]

So you want to find the last row in which a column has data and go to column I regardless of I has any data or not.

Only column B to I have data?

Are you using this ONLY to set the print area?

[nehpets12]

I am only trying to set the print area

but the data I want to print is in columns B C G H or I

I need it to go to the last entry in any of these columns and goto column I so can set the print area a1 to activecell

[Cosmos75]

Then this'll work to select that range.

Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

WARNING: BUT!!! It won't work if you have any data, formula past coumn I.

[ This Message was edited by: Cosmos75 on 2002-04-15 10:58 ]

[AJ]

Still not quite sure I get it, but if the last entry in ANY of columns B C G H or I will get us to the row you want, then use

MyValue = Range("I65536").End(xlUp).Value

to find the number of the last row with a value in column I, then:

Range("I" & myValue).Select

to get to where you want in column I?

Or just
Range("I65536").End(xlUp).Select



[ This Message was edited by: AJ on 2002-04-15 11:02 ]