Results 1 to 6 of 6

Thread: VBA to clear multiple ranges from list of sheets

  1. #1

    VBA to clear multiple ranges from list of sheets



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

    Let me explain.
    How do i get a macro/vba to look at a list of sheets in one worksheet and depending on the data to the right of the name clear the correct range(s).
    for example list would look like
    staff M5, D14:E14
    manager D9:E39, G44:I49
    director B44:E49

    so using the above i'd need to look at the name, i.e staff and it would clear M5, followed by the range D14:E14 then would look and the next name which would be manager and clear D9:e39 followed by G44:I49. it would then look at the next name in the list which using the above would be director.

    The sheets are hidden and have merged cells within the range to be cleared.

    is this even possible?
    any help/pointers would be greatly appreciated.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,250
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    The way I would do it is:

    Set up a named range to cover each of those areas. I.e:
    -Select cells M5 and D14:E14 (at the same time), and name that range as rngStaff
    -Name your other two ranges rngManager and rngDirector respectively

    You'd then clear it with the following macro:

    Code:
    Sub Clear
    With Worksheets("Sheet1")
         .Range("rngStaff").ClearContents
         .Range("rngManager").ClearContents
         .Range("rngDirector").ClearContents
    End With
    End Sub
    The good news then is that you only have to keep the names up to date in the main Excel interface and they will continue to work, even if someone inserts ranges above.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    360
    Articles
    0
    Excel Version
    Excel 2016
    How about:
    Code:
         .Range("rngStaff,rngManager,rngDirector").ClearContents
    or
    Code:
    .Range("M5, D14:E14, D9:E39, G44:I49, B44:E49").ClearContents
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  4. #4
    Quote Originally Posted by Simon Lloyd View Post
    How about:
    Code:
         .Range("rngStaff,rngManager,rngDirector").ClearContents
    or
    Code:
    .Range("M5, D14:E14, D9:E39, G44:I49, B44:E49").ClearContents
    Both excellent answers why i didn't think of naming a range and having it clear
    thanks for that

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,250
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Hey Simon,

    For what it's worth...
    Code:
         .Range("rngStaff,rngManager,rngDirector").ClearContents
    I usually separate each named range into a separate code line. It's personal preference only, but I like to have each line declared explicitly. To me it makes them a bit more obvious and self documenting in th code. You can certainly argue that, of course.

    With regards to:
    Code:
    .Range("M5, D14:E14, D9:E39, G44:I49, B44:E49").ClearContents
    I always avoid this format and always name the range. Again, this will work, but I like the "future proofing" of the named ranges. When I was first learning to code, and before I knew what I was doing, I burned myself a few times by inserting rows, not realizing the VBA code wouldn't update.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  6. #6
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    360
    Articles
    0
    Excel Version
    Excel 2016
    Hi Ken i agree on both counts, i was just showing the OP other avenues, one thing that merits mention is that with named ranges, if you dont make them dynamic then inserting data within the named range wont give you expected results, a great explanation here by Helen http://www.contextures.com/xlNames01.html should help folk understand better.
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

Tags for this Thread

Posting Permissions

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