Results 1 to 5 of 5

Thread: Last row containing Data in a table column

  1. #1
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    718
    Articles
    0
    Excel Version
    Excel 2010 64bit

    Last row containing Data in a table column



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Having an issue when data is deleted from a table. I can't get the last row with data using VBA
    Code:
    LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
    always gives the last row of the table, not the last row containing data.

    The formula =MATCH(9.99999999999999E+307,A:A) gives the row I'm looking for.
    I would prefer VBA to having this in a cell and referencing it.
    So far my coding and Goggling ability has not come up with a VBA adaptation of this formula.
    Your assistance towards a VBA solution would be greatly appreciated.

    Thanks in advance

  2. #2
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    Dr Google to the rescue with lots of testing

    Code:
    ActiveSheet.ListObjects("Table1").DataBodyRange.SpecialCells(xlCellTypeLastCell).Row

  3. #3
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    718
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Hey Doc, please test it on this one.

    The table has had data up to row 20 then some of the entered data was deleted ie:A13 : D20.
    The answer I'm looking for is 12 same as the formula in E1 gives.

    Thanks for looking, mid-night here, I'll check back in the morning.
    Attached Files Attached Files

  4. #4
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    also fail here. not certain why it worked earlier with my test. Bad practice but this worked

    lastwithdata = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).End(xlUp).Row

  5. #5
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    718
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Thanks for testing that Wiz.

    Never thought about two .Ends together like that, but won't be using it.
    Once this project is past it's test stage I doubt this issue will ever come into play, then it would be wrong every time.

    Failing snb arriving with one of his famous one-line solutions think I'll just find the lastrow as per normal, check it for empty and start stepping up the column if need be.

Posting Permissions

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