Share via

F5 delete blanks not working

Anonymous
2012-04-16T03:32:20+00:00

I have a matrix of data. There are blank spaces, that were assigned via an if condition. That is, if the condition wasn't met, I assign a " ".  I copy it on to a new sheet and *paste values*. Then press F5 to select blanks (with the goal of deleting all of the blanks between the cells containing numbers). It claims no blanks are found. 

It is almost as if it is thinking of the data I'm grabbing as an array or that the data is formatted somehow. I did not name it as an array. I say that it is thinking of it as an array since when I press CTRL +SHIFT down arrow --it grabs a consistent outline of the data. Even when that command would typically only go as far as it finds non-empty cells. Here is goes down always to the same place even when the cell appears to be blank.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Ashish Mathur 101.9K Reputation points Volunteer Moderator
2012-04-16T23:22:07+00:00

Hi,

Your description is not clear.  There is nothing called a blank space.  Either there is a blank or a space.  A blank has a length of 0 and a space has a length of 1.  From the symbol you have used, i assume that your IF condition returned spaces i.e. " ".

If that is the case, then after paste values, press Ctrl+H.  In the Find box, enter space.  leave the Replace with box blank.  Press Replace All

Hope this helps.

Was this answer helpful?

2 people found this answer helpful.
0 comments

7 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. OssieMac 48,001 Reputation points Volunteer Moderator
    2012-04-16T08:38:36+00:00

     Thanks.This works but I will have to do these steps for more than 20 rows. 

    I don't understand. If you include all rows in the AutoFilter then it is all one operation. Your comment suggest that you would need to do it 20 times. If AutoFilter is not applying to  the full number of rows due to blanks then select all of the data first before setting AutoFilter and it will be applied to all of the data.

    The secret of Excel (and most programming languages) is to trick it into doing what you want it to do with Work Arounds etc.

    UDF's (User Defined Functions) can often be written to accommodate those areas of Excel that are not already catered for in the package but that entails VBA code.

    As a programmer I am prepared to state that if every little enhancement that everone would like to have is incorporatedwhen they come across a specific problem then it would be an endless job for the programmers to continually update and it is just not practical.

    If you use a character or string in lieu of the "" then you could still use AutoFilter but set the filter to the character or string. Irrespective of whether you use "" or a special character or string you will still have the extra steps of AutoFilter and set the filter to the rows to be deleted and after deleting, turn off AutoFilter.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2012-04-16T07:16:04+00:00

    Thanks.This works but I will have to do these steps for more than 20 rows. 

    A.

    I'd have thought that there would be a custom box in

    F5, go to-> special where your own specification can be entered.

    What if --instead of a space when the if condition is evaluated and not true-- I enter a certain number or txt instead then: F5, go to-> special and select the formulas tab?

    (I tried the character: -  and selected formulas, then txt and it also says: "no cells found").

    B.

    It's strange that it views a space as a string rather than as empty cell.  Do you think there is another way to get excel to recognize " " as empty?

    Was this answer helpful?

    0 comments
  3. OssieMac 48,001 Reputation points Volunteer Moderator
    2012-04-16T06:09:23+00:00

    The "" is NOT seen as a blank cell and when you Paste Special -> Values there appears to be nothing in the cells but Excel does not see them as blank.

    A WorkAround.

    Select one of the cells in the range and Turn on AutoFilter. (Home ribbon -> Sort & Filter -> Filter.)

    Set the filter to Blanks on the required column. (AutoFilter sees these cells as a blank.)

    Select all of the visible rows. (Exclude the column headers)

    On the Home Ribbon click Find & Select (Far right of ribbon)

    .

    Select Go to Special.

    Select Visible cells only and OK.

    Right click on any part of the selection and then click Delete Row.

    Turn off AutoFilter.(Same as turning on; it toggles On and Off)

    Added with Edit:

    You can use F5 In lieu of Selecting Find & Select but still select Visible cells; not blanks.

    Was this answer helpful?

    0 comments
  4. HansV 462.6K Reputation points
    2012-04-16T05:52:53+00:00

    A cell with a formula that returns a space " " (or even an empty string "") is not blank, since it contains a formula.

    Was this answer helpful?

    0 comments