Page 2 of 2 FirstFirst 12
Results 11 to 17 of 17

Thread: VBA mod

  1. #11
    Board Regular
    Join Date
    Aug 2010
    Posts
    180
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA mod

    Quote Originally Posted by Fluff View Post
    Is your data in a structured table?
    yep

  2. #12
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,123
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA mod

    In that case try
    Code:
        If Not Rng Is Nothing Then Intersect(Rng, Sheets("Data").ListObjects(1).DataBodyRange).Delete
    Change the value in red if needed.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #13
    Board Regular
    Join Date
    Aug 2010
    Posts
    180
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA mod

    Quote Originally Posted by Fluff View Post
    In that case try
    Code:
        If Not Rng Is Nothing Then Intersect(Rng, Sheets("Data").ListObjects(1).DataBodyRange).Delete
    Change the value in red if needed.
    is the ListObjects(1) component referring to the table name within the xls? If so, does it need to be as follows - ListObjects("Table13")?

  4. #14
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,123
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA mod

    No it's referring to the number, if you only have 1 table on the sheet then leave it as-is.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #15
    Board Regular
    Join Date
    Aug 2010
    Posts
    180
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA mod

    Quote Originally Posted by Fluff View Post
    No it's referring to the number, if you only have 1 table on the sheet then leave it as-is.
    Spotted it - sheet name is wrong in my VBA, will try again

    Dan
    Last edited by dantheram; Sep 9th, 2019 at 12:17 PM.

  6. #16
    Board Regular
    Join Date
    Aug 2010
    Posts
    180
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA mod

    Quote Originally Posted by Fluff View Post
    No it's referring to the number, if you only have 1 table on the sheet then leave it as-is.

    its working fine when i adjust the ListObjects(#) now. However, i have 2 tables on the sheet which i require it to act upon, so ListObjects(1) and ListObjects(2), how do i repeat the function for both?

  7. #17
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,123
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA mod

    You'll need to change the range it loops through & the listobject number to match the other table
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

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
  •