+ Reply to Thread
Results 1 to 21 of 21

Clear Content Range Based on Cell Value

  1. #1
    Forum Contributor
    Join Date
    05-16-2007
    Location
    USA
    MS-Off Ver
    MS Office 2016, Excel 2016
    Posts
    206

    Clear Content Range Based on Cell Value

    After sorting, I still have many blank cells listed before my data, even though supposedly, when you sort, it's suppose to remove them. I've found a crude way to get around the issue, but I'm stuck because of my limited VBA skills.

    So BS4:BS51 is where my data is sorted. Depending on what data is sorted at the time, I can have many blank cells in this range, which is very frustrating. So I have added a formula to BT3 that tells me how many in this range are blank or "supposedly" blank using this simple formula:

    =COUNTBLANK(BS4:BS51)

    Now I just need a simple vba code that will automatically clear the contents of these first cells in this range based on the number from BT3.

    Example:
    For my current sort, the value at BT3 is 22. So I need Excel to ClearContents for cell range BS4:BS26, which is 22 cells. But I need it to do it dynamically, since there will be many Sorts with differing amounts of "supposedly" blank cells. Fortunately after sorting, all the blank cells are always listed before the data, so it's just a matter of clearing content for the first "supposedly" blank cells.

  2. #2
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Clear Content Range Based on Cell Value

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    10-04-2020
    Location
    New Zealand
    MS-Off Ver
    2013
    Posts
    5

    Re: Clear Content Range Based on Cell Value

    Hi there,

    I have a delete blank rows VBA macro that I use.
    I have attached it for you to have a look at.
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    05-16-2007
    Location
    USA
    MS-Off Ver
    MS Office 2016, Excel 2016
    Posts
    206

    Re: Clear Content Range Based on Cell Value

    Quote Originally Posted by Logit View Post
    Please Login or Register  to view this content.
    Syntax error on this line ---> On*Error*GoTo*NoBlanksFound:

    Also, I'm assuming I need to change your range from B3:B100 to mine, which is BS4:BS51?

    Also, I noticed it is using xlShiftUp for the rows. Want that move the entire row up? If so, that will not work. Everything else on the worksheet must stay where it is. Only the data between BS4:BS51 can be altered.
    Last edited by Modify_inc; 12-17-2020 at 06:29 PM.

  5. #5
    Forum Contributor
    Join Date
    05-16-2007
    Location
    USA
    MS-Off Ver
    MS Office 2016, Excel 2016
    Posts
    206

    Re: Clear Content Range Based on Cell Value

    Quote Originally Posted by l.dekoster View Post
    Hi there,

    I have a delete blank rows VBA macro that I use.
    I have attached it for you to have a look at.
    Thanks for the example. I thought at first I could use it the way it was, but noticed it deletes the entire row if the cell is empty. I'll still study the code and hopefully learn something from it.

  6. #6
    Forum Contributor
    Join Date
    05-16-2007
    Location
    USA
    MS-Off Ver
    MS Office 2016, Excel 2016
    Posts
    206

    Re: Clear Content Range Based on Cell Value

    Quote Originally Posted by Modify_inc View Post
    Syntax error on this line ---> On*Error*GoTo*NoBlanksFound:

    Also, I'm assuming I need to change your range from B3:B100 to mine, which is BS4:BS51?

    Also, I noticed it is using xlShiftUp for the rows. Want that move the entire row up? If so, that will not work. Everything else on the worksheet must stay where it is. Only the data between BS4:BS51 can be altered.
    I fixed the syntax, just needed to remove the *s from the code. Used my range, and it works!. So disregard the xlShiftUp, I just figured it would move the entire row.

    Thanks again!

  7. #7
    Forum Contributor
    Join Date
    05-16-2007
    Location
    USA
    MS-Off Ver
    MS Office 2016, Excel 2016
    Posts
    206

    Re: Clear Content Range Based on Cell Value

    Quote Originally Posted by Modify_inc View Post
    Syntax error on this line ---> On*Error*GoTo*NoBlanksFound:

    Also, I'm assuming I need to change your range from B3:B100 to mine, which is BS4:BS51?

    Also, I noticed it is using xlShiftUp for the rows. Want that move the entire row up? If so, that will not work. Everything else on the worksheet must stay where it is. Only the data between BS4:BS51 can be altered.
    Actually, it's not working. I've tried multiple times with copies of my worksheet, and it always works the first time, but it will never work again afterwards. It keeps saying No Blanks are Found! Yet, I clearly see the same 22 blank cells. If I select the 22 blank cells, then hit delete, then run your code, it works again. So apparently these cells keep changing from truly blank to semi blank. But even when they're semi blank and your code doesn't detect them, the simple Excel function =isBlank still detects there are 22 blank cells. So how can it detect the cells are still blank but not your code?

    Why does it have to be so difficult just to make a cell truly blank/empty so one doesn't have to jump through so much crap just to sort a list of words!

  8. #8
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Clear Content Range Based on Cell Value

    .
    Try this :

    Please Login or Register  to view this content.

  9. #9
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Clear Content Range Based on Cell Value

    Why does it have to be so difficult just to make a cell truly blank/empty so one doesn't have to jump through so much crap just to sort a list of words!
    Why don't you just upload a sample file depicting your actual setup and requirement so that a simplistic solution can be offered...
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  10. #10
    Forum Contributor
    Join Date
    05-16-2007
    Location
    USA
    MS-Off Ver
    MS Office 2016, Excel 2016
    Posts
    206

    Re: Clear Content Range Based on Cell Value

    Quote Originally Posted by sintek View Post
    Why don't you just upload a sample file depicting your actual setup and requirement so that a simplistic solution can be offered...
    I don't mind doing that, but again, it is just a simple sorting of words. I'll upload it shortly.

    All I'm doing is sorting words between D4:AY4, which is 48 words, though some of the cells are blank, or semi-blank since there is a formula in each cell. The formula is basic, it just links to another sheet with the actual words. I do this so it can update automatically and I don't have to keep copying and pasting them.

    So with all 48 words or actually up to 48 since some can be blank, I just want to sort them alphabetically, AtoZ. Unfortunately, Excel seems to make this very difficult, because no matter how I sort them, the blank cells that contain NO words still show up in my sorted list. And yet, supposedly, blank lines are suppose to be removed when you sort. Well, not my blank lines, mine are apparently here to stay and cause me frustration. Also, I find it even more aggravating that some functions like, isBlank, can detect these cells as blank, while others claim there is no blank cells.

  11. #11
    Forum Contributor
    Join Date
    05-16-2007
    Location
    USA
    MS-Off Ver
    MS Office 2016, Excel 2016
    Posts
    206

    Re: Clear Content Range Based on Cell Value

    Quote Originally Posted by Logit View Post
    .
    Try this :

    Please Login or Register  to view this content.
    I get a Run-time error 9; Subscript out of range
    Debugs at the For Loop
    I even tried it with my range, which is BS4:BS51, and still the same error.

  12. #12
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Clear Content Range Based on Cell Value

    .
    Have you changed the sheet name to match yours ?

    Please Login or Register  to view this content.

  13. #13
    Forum Contributor
    Join Date
    05-16-2007
    Location
    USA
    MS-Off Ver
    MS Office 2016, Excel 2016
    Posts
    206

    Re: Clear Content Range Based on Cell Value

    Quote Originally Posted by Logit View Post
    .
    Have you changed the sheet name to match yours ?

    Please Login or Register  to view this content.
    That fixed it! Thanks again!

  14. #14
    Forum Contributor
    Join Date
    05-16-2007
    Location
    USA
    MS-Off Ver
    MS Office 2016, Excel 2016
    Posts
    206

    Re: Clear Content Range Based on Cell Value

    Quote Originally Posted by sintek View Post
    Why don't you just upload a sample file depicting your actual setup and requirement so that a simplistic solution can be offered...
    Here you go. It's crude, but it shows the issue clearly.
    Attached Files Attached Files

  15. #15
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Clear Content Range Based on Cell Value

    Let's forget everything you've done and go back to basics...
    I say again...
    Why don't you just upload a sample file depicting your actual setup and requirement so that a simplistic solution can be offered...
    A file that has your initial before sort and then tell us what you want to achieve...When we say exact setup, we mean exact setup...Ignoring your fix of countblank!

  16. #16
    Forum Contributor
    Join Date
    05-16-2007
    Location
    USA
    MS-Off Ver
    MS Office 2016, Excel 2016
    Posts
    206

    Re: Clear Content Range Based on Cell Value

    Quote Originally Posted by sintek View Post
    Let's forget everything you've done and go back to basics...
    I say again...


    A file that has your initial before sort and then tell us what you want to achieve...When we say exact setup, we mean exact setup...Ignoring your fix of countblank!
    I'm assuming maybe you didn't see the Sort button on Sheet2 over BO and BP. It is pretty far over to the right. When you press it, it sorts the words, leaving many blank cells before the actual words.

    I actually have a formula that resolves this issue but it fails to work correctly when there is a formula in a cell rather than just a word.

    Before, I was just copying and pasting all the words over from Sheet1 to Sheet2.

    I figured, what the hell, why not just link to the cells on Sheet1, thus no more copying and pasting. Now I'm about ready to just go back to copying and pasting since Excel is being difficult.

    The only thing that has changed is instead of an actual word in the cells, they link to the actual word from another cell with a formula. And with that small change, Excel can't for some reason remove what should be considered blank cells, or even just rename the blank cells as I do with this formula below. But using the linking formula for the words that I described earlier, it also doesn't work with this formula.

    IF(INDEX(Game!$G:$BB,MATCH(Game!$A$3,Game!$C:$C,0),ROWS(BF$4:BF4))=0,"zz--",INDEX(Game!$G:$BB,MATCH(Game!$A$3,Game!$C:$C,0),ROWS(BF$4:BF4)))

    With the "zz--" I could at least sort all the blanks to the end, but when using the linking formula for the words, which is in the file I uploaded, I just get blanks, and unfortunately, blanks get pushed to the front when sorting A/Z, which is my main aggravation.

  17. #17
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Clear Content Range Based on Cell Value

    I don't think you understand...

    Supply us with a sample file depicting your Before Scenario and then show us what you expect Afterwards...
    Mock up your result manually if you have to...
    Ignoring everything you are or have tried...

    In your sample file you supplied...Col's A - C are blank, Rows 1 to 3 are blank...
    Your data starts in D4....Words A to Z...Which is already in order...Is this the actual setup of your file...and the range you want to sort...

    Please Note…
    Quoted From Rick Rothstein

    One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data). To sum up... we only know what you tell us, nothing more. Also, uploading an actual file (see yellow banner near the top of this forum's window) showing your data as it exists and on another sheet showing how you want it to look after being processed would help immensely.

  18. #18
    Forum Contributor
    Join Date
    05-16-2007
    Location
    USA
    MS-Off Ver
    MS Office 2016, Excel 2016
    Posts
    206

    Re: Clear Content Range Based on Cell Value

    Quote Originally Posted by sintek View Post
    I don't think you understand...

    Supply us with a sample file depicting your Before Scenario and then show us what you expect Afterwards...
    Mock up your result manually if you have to...
    Ignoring everything you are or have tried...

    In your sample file you supplied...Col's A - C are blank, Rows 1 to 3 are blank...
    Your data starts in D4....Words A to Z...Which is already in order...Is this the actual setup of your file...and the range you want to sort...

    Please Note…
    Quoted From Rick Rothstein
    It's very possible after reading the quote from Rick Rothstein, that I assumed when/where I shouldn't have. I did provide a few notes in the sample but after going back over it, I can see it needs more directions.
    I'm assuming you didn't see my Sort button I created on Sheet2 because I think if you had it would have cleared most if not all of this up. I forgot to mention it in the sample file, so definitely guilty of assuming it would be found. Though I did reply earlier to you to where it is located, at least I think it was you. It's on the second sheet, Sheet2, right under BO and BP. In any case, I will be uploading an updated sample file shortly. I think once you find my Sort button and click it, you will see the issue I'm having with all the blank lines that are sorted with my data.

    All I want to do is remove the blank lines or at minimum make them appear last.

  19. #19
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Clear Content Range Based on Cell Value

    Saw your button....Saw your code...

    See post 9 & 15 & 17...
    Ignore your code etc...

    Supply a sample file of your data before this supposed code runs and then what the result should be after the code has run...
    This way we will be able to ascertain what it is you are actually trying to accomplish...
    So...in other words...Assume you have NO CODE AT ALL and are asking for a snippet to do what you want to achieve...
    You are looking for a fix to YOUR code...Let us supply simplistic code to achieve the required result...
    Last edited by sintek; 12-19-2020 at 02:05 PM.

  20. #20
    Forum Contributor
    Join Date
    05-16-2007
    Location
    USA
    MS-Off Ver
    MS Office 2016, Excel 2016
    Posts
    206

    Re: Clear Content Range Based on Cell Value

    Quote Originally Posted by sintek View Post
    Saw your button....Saw your code...

    See post 9 & 15 & 17...
    Ignore your code etc...

    Supply a sample file of your data before this supposed code runs and then what the result should be after the code has run...
    This way we will be able to ascertain what it is you are actually trying to accomplish...
    So...in other words...Assume you have NO CODE AT ALL and are asking for a snippet to do what you want to achieve...
    You are looking for a fix to YOUR code...Let us supply simplistic code to achieve the required result...
    Here you go, and thanks in advance, and most of all, your patience!
    Attached Files Attached Files

  21. #21
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Clear Content Range Based on Cell Value

    Sheet2 is where the Formulas come into play and you can find my Sort button located under BC & BD.
    I don't think you understand what I've been asking in all my above posts...
    I am not interested in sheet2 and your formulas or fix...
    Is sheet 1 original data...Range D4:AY4 ?
    If not, what is original data and what result are you looking for...???
    What do you want to do with this data...???

    If it is just a matter of copying, transposing, sorting...then this will suffice...Other options are also available...
    Please Login or Register  to view this content.
    Or if you just want to sort that range...
    Please Login or Register  to view this content.
    Anyway, won't know until you are transparent with your actual required result...
    Attached Files Attached Files
    Last edited by sintek; 12-20-2020 at 03:29 AM.

+ 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. Clear Rows Content Based on Cell value and key word
    By Excelnewb1567 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-14-2018, 07:05 PM
  2. [SOLVED] Macro to Clear Cell Content based on a condition
    By pchugh in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-23-2018, 08:48 PM
  3. Clear cell content based on cell values
    By Excelski in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-09-2017, 11:25 AM
  4. Clear cell content based on calculated value in another cell
    By DVP2010 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-02-2014, 11:13 AM
  5. Replies: 3
    Last Post: 08-16-2014, 09:55 AM
  6. [SOLVED] Clear Content if cell in a range contain #N/A VBA
    By alex_shin in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-16-2014, 06:33 PM
  7. Replies: 1
    Last Post: 06-21-2013, 04:05 AM

Tags for this Thread

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