Struggling to acomplish something seemingly simple in MS Excel

One

One

One

Soldato
Joined
24 Aug 2011
Posts
6,162
Location
ABQ, NM
My blank cells aren't truly blank! This causes a problem seeing as I have maybe 6000 rows of data, however some just hold a name in one column but no data in the columns next to it. I weed it out by using a simple 'IF(AND(' formula and thus get just the rows with data. However, when I then copy these columns and paste special (values) it turns out the blanks aren't blanks. This means I can't simply F5 select blanks and then delete.

The only option is to replace the 'blanks' with a string, then replace that string with blanks thus making them proper blanks.

So what I'm asking is why do I have to Ctrl-H my fake blanks into real blanks?

See the picture for a better explanation.

rirlhu.png


p.s. the numbers are those words' scrabble scores.
 
The Problem is as soon as you put a formula into a cell it is no longer blank. Even if you force it to show nothing by forcing a blank string "", by pasting values it is not a blank or null cell.

To get round it just put a number in instead of a blank string. So 0 instead of "". Then when you hit F5 you can select numbers instead of blanks and delete as you wanted to.
 
That's not really the issue, the ISBLANK is just there to demonstrate it's not blank. What I really want to do is (considering IRL i'll have over 6000 rows with blanks dotted all over the place) is highlight the columns press F5 then special, then select the blanks so I can delete them and be left with all the rows in the same order but without the blanks separating them. Obviously I can do this by using ctrl+h twice but surely I shouldn't have to do this? It must be possible to create a true blank with a formula no?

To the above poster: Assume the words have now changed into account numbers, it is impossible to differentiate a 0 from an account number. I suppose instead of "" or 0 i could just use an errror NA() and then I'd only have to replace #N/A with a blank, but having to use ctrl+h at all feels like a bit of a bodge! Maybe using a bodge is just what I'll have to do...
 
Last edited:
Well if they changed to account numbers I would use still use "" and then select text instead of blanks. The only problem would come is if you had a mixture of numbers and text strings in the list.
 
Malakite said:
Well if they changed to account numbers I would use still use "" and then select text instead of blanks. The only problem would come is if you had a mixture of numbers and text strings in the list.
Click to expand...

What would you do in this situation?
 
Sub Delete_Blank_Rows()

Range("E3").select
Do
If activecell="" then
Activecell.entirerow.delete
Else
Activecell(2,1).select
End if
Loop until Activecell(1,-2) = ""

End Sub
 
Last edited:
desires said:
That's not really the issue, the ISBLANK is just there to demonstrate it's not blank. What I really want to do is (considering IRL i'll have over 6000 rows with blanks dotted all over the place) is highlight the columns press F5 then special, then select the blanks so I can delete them and be left with all the rows in the same order but without the blanks separating them. Obviously I can do this by using ctrl+h twice but surely I shouldn't have to do this? It must be possible to create a true blank with a formula no?

To the above poster: Assume the words have now changed into account numbers, it is impossible to differentiate a 0 from an account number. I suppose instead of "" or 0 i could just use an errror NA() and then I'd only have to replace #N/A with a blank, but having to use ctrl+h at all feels like a bit of a bodge! Maybe using a bodge is just what I'll have to do...
Click to expand...

You could measure the length of the data in the cell, if it returns zero then it's empty so '=len()' instead of '=isblank'. My other solution of changing the true value to " " and then checking for that value would also work.
 
He still won't be able to use F5 that way though.

Basically there is no way to use F5 if the OP wants to be able to auto select if there is a mix of text and numbers in column A.

Otherwise if there is only text in column A, your original answer of using 0 is the best way without VBA, or if there are numbers in column A you'd use "X" or something in the IF statement and then select text using F5.
 
=OR(ISBLANK(F3),F3="")


?

I feel I'm missing something, but it's worth nothing a cell is not blank when it contains a string, even if the string is 0 in length. I think you'll probably need a more creative solution.
 
Last edited:
Antar Bolaeisk said:
Couldn't he just bring it to a new page where only the lines containing 'False' are carried over negating the need to delete the rows.
Click to expand...

This is a lot of hassle to simple remove rows that should be blank by using F5. I'll just use ctrl+h twice, it's not really a hassle I just assumed there would be a way to format a cell as blank (not a string of length 0) using an excel forumla, apparently there isn't though.

What I'm struggling to understand is for what purpose MS would chose to distinguish between a blank cell and a cell that is blank but a string.
 
Your formula in G2 is wrong.

You do not need to paste values in column F.

Instead replace with this formula:-

=IF(ISBLANK(E2),"",E2)
 
Last edited:
Why do they need to be blank? Why not just populate the cell with (e.g.) "DELETE", and then instead of filtering to blanks you filter to DELETE?
 
You must log in or register to reply here.
Back
Top Bottom