Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 21

Thread: Find Last Data In Columns

  1. #11
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #12
    Board Regular
    Join Date
    Feb 2002
    Posts
    449
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #13
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  4. #14
    Board Regular
    Join Date
    Feb 2002
    Posts
    449
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    There is no data after column I

  5. #15
    Board Regular
    Join Date
    Mar 2002
    Location
    =ActiveCell.Address
    Posts
    478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #16
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #17
    Board Regular
    Join Date
    Feb 2002
    Posts
    449
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #18
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  9. #19
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Al Chara,

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

  10. #20
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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