+ Reply to Thread
Results 1 to 8 of 8

Macro doesn't work in Table

  1. #1
    Forum Contributor
    Join Date
    02-26-2015
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    248

    Macro doesn't work in Table

    I have various macros that all work except when I try to use them with data in a Table.
    First time I have come across this.
    Is there something I need to do different with code(s) when I try to run it in a Table.
    Mini sample WS is attached. One is a Table and one isn't. Please note that either WS needs to be named MasterData for the code.
    The code removes rows that have blanks in Col. K.
    Thanks in advance,

    Brenda
    Attached Files Attached Files
    Last edited by 6StringJazzer; 03-28-2018 at 09:19 AM. Reason: Moved from Excel General

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Macro doesn't work in Table

    When you Autofilter a range that is in a table, Autofilter is applied to the entire table, not just the range you specified (column K in this case). To fix this, specify column K as the field for the filter criteria. The Field number is the number of the column within the table; B is the first column so K is 10:

    Please Login or Register  to view this content.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Macro doesn't work in Table

    nevermind reply already posted. But the code does delete the row below the specified range.
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Macro doesn't work in Table

    Quote Originally Posted by Arkadi View Post
    But the code does delete the row below the specified range.
    The With range is not a single row but a range of rows starting with row 1 and going to the last row containing data. The Offset used for deletion refers to the range starting with the second row and extending to the row following the last row of data. It's harmless to delete that extra row because it's blank anyway.

  5. #5
    Forum Contributor
    Join Date
    02-26-2015
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    248

    Re: Macro doesn't work in Table

    Thank you for the explanation Jeff. But is there a way of deleting the rows with blanks instead of using the filter?

  6. #6
    Forum Contributor
    Join Date
    02-26-2015
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    248

    Re: Macro doesn't work in Table

    Yes but it's a huge file - lotta different code running on it and so far 160K rows. The blank rows do get incorporated into the data and it does affect the bottom number. I realy need those suckers out and not just hidden.

  7. #7
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Macro doesn't work in Table

    Try this. Lightning fast. No longer cares about tables, no filtering required.

    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    02-26-2015
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    248

    Re: Macro doesn't work in Table

    BOOM! Perfect. Thank you!

+ 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. TextJoin in a table doesn't seem to work
    By Bearco in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 03-31-2017, 12:13 AM
  2. [SOLVED] Macro work in personal workbook, doesn't work in other workbooks
    By Centexcel in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-30-2013, 11:47 AM
  3. Static date doesn't work if I use form to fill a table
    By Numbi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-31-2013, 07:36 AM
  4. [SOLVED] Macro Doesn't Work Through Button, Does Work Through Developer ->Macros Option
    By freybe06 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 06-27-2013, 11:55 AM
  5. [SOLVED] Prereq table reverse... VLOOKUP doesn't work
    By Hambone70 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-24-2013, 01:29 PM
  6. Replies: 3
    Last Post: 07-01-2012, 09:38 AM
  7. Conditional formatting applied to a pivot table doesn't work as it should
    By Pichingualas in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-14-2012, 07:43 AM

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