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.

Please Login or Register  to view this content.

Hi there,

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

Originally Posted by l.dekoster

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.

Originally Posted by Modify_inc

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!

Originally Posted by Modify_inc

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!

.
Try this :

Please Login or Register  to view this content.

Originally Posted by sintek

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.

Originally Posted by Logit

.
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.

.
Have you changed the sheet name to match yours ?

Please Login or Register  to view this content.

Originally Posted by Logit

.
Have you changed the sheet name to match yours ?

Please Login or Register  to view this content.

That fixed it! Thanks again!

Originally Posted by sintek

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.

Originally Posted by sintek

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.

Originally Posted by sintek

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.

Originally Posted by sintek

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!