Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: Find Last Data In Columns

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

    Default

    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.

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

    Default

    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

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

    Default

    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 ]

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

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

    nehpets12: Does your data stop at column I?

  5. #5
    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,

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

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

    Default

    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


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

    Default

    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?

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

    Default

    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


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

    Default

    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 ]

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

    Default

    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 ]

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
  •