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
    805
    Articles
    0
    Excel Version
    Excel 2010

    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
    Excel Version
    Office 365
    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
    805
    Articles
    0
    Excel Version
    Excel 2010
    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
    Excel Version
    Office 365
    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
    805
    Articles
    0
    Excel Version
    Excel 2010
    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
  •