+ Reply to Thread
Results 1 to 5 of 5

Macro to clear cells in range & move up if certain cells meet criteria

  1. #1
    Registered User
    Join Date
    03-01-2011
    Location
    Jersey
    MS-Off Ver
    Excel 2008
    Posts
    22

    Macro to clear cells in range & move up if certain cells meet criteria

    Hi.

    I'm an extreme novice at VBA, so any help would be greatly appreciated.

    I have a workbook containing 50 sheets of data for my friend's business. She just told me that certain items are not available on certain configurations.

    Essentially, items with an Interior of GUS or 3R (column G) are only available if the Configuration (Column I) is = to Double Panel.
    Therefore, I search the data in a given range. If Column G = GUS or 3R and Column I is not equal to Double, then I need to clear the data from the cells (for example in the sample sheet that I'm attaching, A26:M26) and shift the cells underneath up.

    I can't shift the entire row up because I have information in other columns to the right that I need to stay in place.

    Is this possible with a Macro? If not, at a minimum, would a nested if formula work to clear the data from columns L & M and then I can manually move the cells up?

    Thanks in advance for any suggestions. See attached example.
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2405 Win 11 Home 64 Bit
    Posts
    23,890

    Re: Macro to clear cells in range & move up if certain cells meet criteria

    This is achievable with VBA, but I am confused by your statement to move up cells. Which cells should be moved up and if doing so instead of deleting the row, will this not mismatch the items in your tables? I am a bit confused by the moving up statement. Please clarify with a specific example of what you mean to happen.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2405 Win 11 Home 64 Bit
    Posts
    23,890

    Re: Macro to clear cells in range & move up if certain cells meet criteria

    Reread your requirements.

    Try this:

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    03-01-2011
    Location
    Jersey
    MS-Off Ver
    Excel 2008
    Posts
    22

    Re: Macro to clear cells in range & move up if certain cells meet criteria

    Thanks for the response. And please forgive me if I'm a little inarticulate in explaining.

    If I were manually clearing the cells without using a Macro, I could manually select the cells, click on Edit > Delete > Shift Cells Up.

    Does this screen shot help?
    Screen Shot 2016-07-09 at 9.01.50 PM.png

    Thank you again for helping. I truly appreciate your time.

  5. #5
    Registered User
    Join Date
    03-01-2011
    Location
    Jersey
    MS-Off Ver
    Excel 2008
    Posts
    22

    Re: Macro to clear cells in range & move up if certain cells meet criteria

    You are the BEST! Thank you so much! Saved me a bundle of time!

+ 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] COUNTIF on range of cells that meet 2 criteria
    By mattsafact in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-06-2016, 10:54 AM
  2. [SOLVED] Help with loop to select range of cells that meet criteria
    By NU2vba in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-10-2016, 02:27 AM
  3. [SOLVED] Deleting rows where range of cells meet two criteria
    By dubcap01 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-23-2015, 04:50 AM
  4. [SOLVED] Skip cells and move onto next cell that don't meet criteria using IF fucntion in Excel
    By sprewett in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 07-06-2014, 07:49 AM
  5. Paste range of cells that meet criteria
    By lorena.tgarza in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-04-2012, 09:51 PM
  6. move row data to a new tab when cells meet specific criteria
    By brian807 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 10-10-2012, 10:18 AM
  7. sum a range of cells that meet one criteria
    By shanevo6 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-10-2007, 04:06 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