Thanks:  0
Likes:  0

Thread: Find Last Data In Columns

1. 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?

2. 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

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

4. There is no data after column I

5. 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?

6. Aj,

I had your same idea, but changed it around a bit. Try the following:
Code:
```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 ]

7. 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

8. 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

9. Al Chara,

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

10. 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.

User Tag List

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•