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.

 

[Cosmos75]

AJ,

I think he is looking for the last row with data in any of the columns B to I, and he's going to Column I to be able to set a print area of A1 to I(Last row with data in Column B to Column I) to ba able to set the print area to include all data.

Am I right?

 

[nehpets12]

I only need to find the end data in "b" "c" "g" "h" or "i" I have data in columns D E F but I want ti ignor the data in these columns hope this is clearer

Thanks for your help in advance

 

[Al Chara]

Is there any data after column I (i.e. is there data in column J)?

 

[nehpets12]

There is no data after column I

 

[AJ]

Here's another try.
Is this right???
Regardless of any other column on the sheet we're only interested in finding out which of columns B, C, G, H and I has the greatest row number with data in it, and then selecting the cell in column I that corresponds to that row number?

i.e. if column B finished in row 6, column c finishes in row 8, column G finishes in row 16, column H finished in row 12 and column I finished in row 10, then the cell to select would be I16, because that's the greatest row number??

If so, try this...

LastB = Range("B65536").End(xlUp).Row
LastC = Range("C65536").End(xlUp).Row
LastG = Range("G65536").End(xlUp).Row
LastH = Range("H65536").End(xlUp).Row
LastI = Range("I65536").End(xlUp).Row


LastOfAll = LastB

If LastC > LastOfAll Then
LastOfAll = LastC
End If
If LastG > LastOfAll Then
LastOfAll = LastG
End If
If LastH > LastOfAll Then
LastOfAll = LastH
End If
If LastI > LastOfAll Then
LastOfAll = LastI
End If

Range("I" & LastOfAll).Select


Am I any nearer now?

 

[Al Chara]

Aj,

I had your same idea, but changed it around a bit. Try the following:

Dim LastRow As Integer
RowArray = Array(2, 3, 7, 8, 9)
For Each i In RowArray
LastRow = Cells(65536, i).End(xlUp).Row
Cells(i - 1, 10) = LastRow
Next i
LastRow = Application.WorksheetFunction.Max(Columns("J"))
ActiveSheet.PageSetup.PrintArea = "$A:$I$" & LastRow

_________________
Hope this helps.
Kind regards, Al.
This message was edited by Al Chara on 2002-04-15 12:06
This message was edited by Al Chara on 2002-04-15 12:21
This message was edited by Al Chara on 2002-04-15 12:25

 

[nehpets12]

THANK YOU AJ just what I wanted its a pity I did not explane myself better the first time thanks for staying with me and for EVERYONES help

Stephen

 

[Al Chara]

Disregard my previous response, I finally got my head out of my butt. You can also try the following response (should be accurate now):

Dim LastRow, ThisRow As Integer
RowArray = Array(2, 3, 7, 8, 9)
For Each i In RowArray
ThisRow = Cells(65536, i).End(xlUp).Row
If ThisRow > LastRow Then LastRow = ThisRow
Next i
ActiveSheet.PageSetup.PrintArea = "$A$1:$I$" & LastRow

 

[Cosmos75]

Al Chara,

What a nice and neat reformulation of AJ's code! Why aren't you an Excel MVP??

 

[Al Chara]

Thanks Cosmos75 for the nice words, but I am no where near the level that some of these guys/girls are at. Learning more everyday, though.