+ Reply to Thread
Results 1 to 9 of 9

Find Last Row for Table Size

  1. #1
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Find Last Row for Table Size

    Hi,

    I'm trying to automate through VBA the creation of a table.

    The problem is I have blank rows in the raw data. I can't even apply a filter and delete the blanks because the filter stops when it hits the blank row and doesn't include the rest of the data.

    At the moment I have to manually create a table with the dimensions of A1 through to the last column and row of the entire range, I can then filter out the offending blanks. But I would like to cut out the manual table creation.

    Thanks

  2. #2
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Find Last Row for Table Size

    Hi,

    Is there one column that should always have data for a valid row?
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  3. #3
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Find Last Row for Table Size

    No the entire row is blank.

  4. #4
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Find Last Row for Table Size

    I appreciate that. What I mean is, for a row that has some data, is there a specific column that would always be populated?

  5. #5
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012
    Quote Originally Posted by xlnitwit View Post
    I appreciate that. What I mean is, for a row that has some data, is there a specific column that would always be populated?
    Yes, there are columns that always have data. In one particular column the data is always the same text. If I try and filter on this column though the filter stops working one it hits the blank line.

  6. #6
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Find Last Row for Table Size

    In that case you can use that column to specify the last row of actual data, and either create the table from the relevant range, or use SpecialCells to delete the blank rows first. Absent any real information as to the layout of your worksheet, that is about as specific as I can be.

  7. #7
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Find Last Row for Table Size

    I've attached some demo data. I want the table to go to the last row with data in it.
    Attached Files Attached Files

  8. #8
    Forum Contributor tax112's Avatar
    Join Date
    02-28-2013
    Location
    Thailand
    MS-Off Ver
    Excel 2003 | 2010
    Posts
    383

    Re: Find Last Row for Table Size

    Quote Originally Posted by kersplash View Post
    I've attached some demo data. I want the table to go to the last row with data in it.
    Try:
    PHP Code: 
    Sub Test()
        
    Dim LR As Long
        LR 
    Cells(Rows.Count"A").End(xlUp).Row
        MsgBox LR
    End Sub 

  9. #9
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Find Last Row for Table Size

    Cool thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Set Table Size?
    By SomeDude0nline in forum Excel General
    Replies: 16
    Last Post: 08-13-2014, 10:01 AM
  2. Macro to "Pull Down or Up" a Table based on Pivot Table Size
    By rmmohan in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-10-2013, 09:16 AM
  3. Edit, Find fuction size issue
    By Todd Lange in forum Excel General
    Replies: 1
    Last Post: 05-13-2009, 10:03 AM
  4. [SOLVED] Auto size a pivot table to a page size
    By *.* in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-23-2006, 12:09 AM
  5. [SOLVED] How to find avg for step size?
    By Omkar in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-17-2006, 11:10 AM
  6. cell size and find increment
    By jimbo_jones in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-21-2005, 10:05 AM
  7. [SOLVED] any way to find out the historical high of mailbox size
    By PinkCrib in forum Excel General
    Replies: 1
    Last Post: 05-27-2005, 12:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1