Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

Go To Blanks Doesnt Highlight All Blanks

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More


Please help this is driving me mad!

The problem is, I do go to blanks and loads of seemingly blank cells are not highlighted. The cells previously contained formulas which are no longer present (I copy/paste special'd)

In the blank cells which are not highlighted there are no spaces. If I say 'text-to-columns, format general' nothing happens, however if I double-click in each troublesome cell they get picked up as being blank.

I also tried copy/paste multiply to no avail.

Any ideas appreciated.



View Answers     

Similar Excel Tutorials

Ignore Blanks in a Data Validation List in Excel
I will show you 3 ways to remove the blanks from a Data Validation dropdown menu in Excel. All 3 methods are multi ...
Delete All Empty Rows or Blank Cells from a Range in Excel
How to quickly delete all empty cells or rows from a range in Excel.  This allows you to quickly clean your data to ...
Connect the Line for a Chart in Excel and Skip Blanks
How to prevent a line chart from having a blank spot or also dropping to zero when there is a blank cell or a zero ...
Find the Next Blank Row with VBA Macros in Excel
Learn how to find the next empty cell in a range in Excel using VBA and Macros.  This method will skip any blanks ...

Helpful Excel Macros

Highlight Cells with Text or Formulas (non-empty cells)
- This macro will highlight all cells in excel which are not empty. This means that if a cell contains formulas, text, nu
Automatically Highlight the Active or Selected Cell
- This macro will highlight the active cell in any excel spreadsheet with a color selected in the code. This means that a
Format Cells in The General (default) Format in Excel Number Formatting
- This free Excel macro formats a selected cell or range of cells to the General number format in Excel. This is the defa
Enter Text/Characters with a Double-Click of the Mouse
- This macro allows you to enter any text or number, that you specify, in a cell on which you double-click. In the exampl
Delete Blank Rows in Excel
- This is a macro which will delete blank rows in excel. This version will delete an entire row if there is a blank cell

Similar Topics

Hello everyone.
I'm trying to select all blank cells (Excel 2010) in a column. I've run a macro that gathers data from a report, but when certain criteria are met the row is left blank (using =if(this, then, ""). I've then copy/paste special/values-d the 5 columns of gathered data to a new worksheet.

Now, I want to delete all rows that have a blank cell in column A. I highlighted column A, clicked Home>Find & Select>Go To Special and selected Blanks.

But it doesn't find anything.

I thought maybe there was just a space in those cells, but when I go to Find, and try to search for blanks (I tried searching for both " " and * *), and, in find options, selected 'match entire cell contents', it finds nothing.

If I select some of the blank cells and hit Delete, then the Go To Special / Blanks finds those cells.

Any ideas?

I have a column with formulas. some formulas return blank cells and some return data.
I copy this column and Paste Special it as Values then I select these values and press F5 and select Blanks from Special tab options (infact I want to remove blank cells) but it returns a msg that "No cells were found" despite having blanks in the range. what's wrong with this.

however when I press delete on any blank cell and then use Go To command to select blanks it then selects only that cells upon which I pressed Delete.

please explain why Excel do not recognise Pasted as values cells as blanks.

Okay, this one is driving me crazy. I have a macro that creates a pivot table, then copies it to another spreadsheet by doing a "Paste Special - Values", followed by a "Paste Special-Formats". My problem is that column A contains text values instead of numbers, and blanks which I want to fill in. When I select that column and use the routine Edit>Go To>Special>Blanks, then hit the " = "sign, then up arrow, then ctrl-shift-enter, the blanks fill in on only to the next text number, not all the way down the column. I know the problem there is that the numbers need to be numbers, but I don't know how to programmatically do that.

If I first select the column and multiply it by 1, the blanks are replaced with zeros, and again my routine won't work to fill in the blanks, 'cause they're no longer blank.

If I insert a column and use a formula such as Code:


and then copy that column and paste the values back into column B, it looks like everything is right, except I still need to fill in the blanks. However, going to Edit>GoTo>Special>Blanks tells me there are no blanks.

I've attached my spreadsheet so you can see what data I'm working with. I realize this must be simple, but it's simply beyond me right now. Thanks in advance for any help I can get on this.

I am trying to select all blank cells. im not sure how or why (must be a result of lots of copy/paste) many of my blank cells are not highlighted when I do the function:
find>go to special>Blanks>OK

not sure how to fix this problem....the spreadsheet is huge and i need to select and delete all cells that do not have visible data. cant seem to figure it out.

can anyone help?!


OK I have a list like the first one below with blank rows between the data. I want to copy and past and have a list like the secpmd one below with all the blanks removed. I tried copy, and paste special with skip blanks checked but that didn't work - it just pasted like the original column with the blanks. Any help would be appreciated






Hi there

I have a problem when trying to use the goto menu to highlight blank cells. I used the find and replace form to remove certain values from cells. However Excel doesn't think these cells are actually blank. Is there a way I can make it so they really are blank and would be highlighted on the "goto blanks" selection.

I found a similar thread to this however the solution was quite unclear and did not seem to work so any help would be great.


Occasionally I copy data in a column to another sheet that is prepared to rank the values with the simple RANK function. Because my data can vary, sometimes BLANK cells are copied over. To ensure formatting is preserved, I always paste as value in this copy-paste. For some stupid reason, excel always sorts the blank cells (typically the bottom of the column) towards the top. I've double and triple checked when this occurs that there is absolutely no data in the blank cells, not even a space. It is the equivalent of ="", and is most certainly not =" ". Even when I ensure that sorting blanks is nto included, or attempt to sort blanks to the bottom, it always managers to somehow stupidly sort blanks to the top. Is there a reason for this?

Hello all,

I have a large table of data, and would like to fill in all blank cells with a simple 0. However, when I highlight the whole table and go to the edit -> go to- > special -> blanks function it returns the error that no blank cells were found. I've tried to copy/paste special to remove any formulas from the data, but that doesn't seem to work, either. Nor does moving the data to a new spreadsheet. It just keeps telling me that there are no blank cells, which is clearly not true.

Is there a special trick that I'm missing or something else I need to do?

Any help is much appreciated, thanks.


See the attached worksheet. What I WANT to do is, in columns A and B, do an Edit/GoTo/Special/Blanks, CtrlEnter to select all, =, up arrow, enter to paste the value above in all of the blank cells. The problem is all of my blank cells appear to be non-blank. I've tried using the TRIM function on them, the CLEAN function, copy and paste as values, etc. Can anyone help me to replace the non-empty blank cells with empty blank cells? Thanks in advance.

Dear Experts,
In my workbook, when I do Auto filter on columns the terms such as
"Blanks", "Non-blanks" does not appear when i sort for auto filter.

My reqyuirement is i need to filter with blank entries in my worksheet. i am not able to perform this since those two options "blanks" and "non-blanks" is not highlighted in the Autofilter tab.
*** Tthere are so many blank cells inbetween , but even then problem.

Can anybody give a solution


I use a formula to calculate values or use set criteria to yield blanks.
Then I graph the data and want the line graph to end where a blank occurs
rather than connect the line to zero where the blank occurs. Actually all
blanks are plotted as zeros, but I could enter -1 instead of blank and make
the scale minimum zero to eliminate this problem if the line did not connect
from the last data point to the -1 or zero.

This problem is not cured by copy>paste special>values even though the
blanks then no longer contain formula but look indistinguishable from real
blanks. But they do not behave as real blanks, for example, when hitting
end>down arrow.

I have a problem here and my head hurts in figuring this out. Up to now, I'm still a newbie when it comes to VB macro programming. There's just too many object and command for me to read before I can learn all these.

This may be challenging, easy for some but very hard for me.

I need a macro that will search from A1 to A1000 and look for blanks in that row. once it find that blank cell, it needs to copy that row, starting from B to K and paste it in a row before that blank cells but it needs to paste it in column L and if theres still blank after that one, it needs to paste the succeeding rows in still the row before the blanks and this time it needs to be paste in column W

This is really hard for me. Can someone help please?


I have a workbook in which formulas are used to either return a value or a blank depending on some condition. Now I wanted to remove all the blanks at one go and was recording a Macro like this:

1-Selected the range of cells in which the formulas exist so eg A1:W50
2-Press F5 and then choose Special and within that Blanks. Then when I say Ok it says 'No cells were found' I think this might be happening because the blank cells have a formula behind them which is spitting out the blank.

So is there an alternate way to do this?


Up until a few weeks ago I was able to copy a cell(s) and then paste them in
another group of cells, usually a column. Presently when I highlight a cell,
right click copy, highlight a very large group of cells, and then right
click, the paste or paste special are grayed out. When I return to the
original cell I can see that it is no longer highlighted. I have found that
if I try to paste only a small group of cells that it will work. I have also
found that I can highlight a cell and many times it will only stay
highlighted for 10-15 seconds. I am using Excel 2000. Ideas and thoughts
will be appreciated.

Hi all,

I have a formula in A1:A10 that returns either a value or a "".

The result will be copied paaste special values in another cell, B1;B10.

Then I will copy B1 and paste specia, value and SKIP BLANKS in C1:C10

Problem is that excel pastes all the cells in C1:C10 i.e. it does not consider any cell as blank although there are soe with no value (as a result of the "" in the original formula).

Is there anyway for force excel to treat those as blank in such a way that it will work when I do paste special SKIP BLANKS?


I created a huge data analysis file that consists of 50 by 214 cells.
According to my formula's, it will only show data if a lot of demands are
met. In reality, this means, about 15 to 20 of them have data, and the others
are blank.

I now want to quickly remove all the blanks. However, Go To>Special>blanks,
doesn't work, because for some reason, Excel doesn't consider my blanks to be
blank, even after I copied the values to another worksheet, and am certain
that these cells are actually blank and empty.

Soooo, do any of you experts know a solution for this problem? Or another
way to quickly gather these appx. 20 cells that do have data in them in one
column? Without blanks? I'd hate to do that manually.

Thank you in advance. I'll be in bed really soon, cause it is terribly late
in Europe, but I'll be back first thing in the morning to check your ideas,
and provide feedback or add info I forgot to post right away...

So here is my code:

Please Login or Register  to view this content.

The goal is for it to continue to copy NON-BLANK cells until it hits another bold item. Bold items are the Businesses I am working with and underneath each bold section is a list of rows with various numbers, however some rows are blank and I want it to skip these rows and go onto the next to copy until it hits the bold title of the next business.

So far this is doing something it can void blanks if it is only 1 blank in a row between two values, however when multiple blanks are present it still copies and pastes blank values.

Help, I need it to not paste blanks into the list on Sheet1.

I am using the following procedure to fill in blank cells with the value above:

Select the column with the blanks.
Goto Edit>Goto...Special and select blanks.
Goto the formula bar, enter =A1 (or whatever the first cell with data is) and then press CTRL+ENTER.
Select the column, copy and paste special values

However this does not work with what I need to do now.

What I need to do is this:



This means I need to fill in the blank cells with the SERIES OF VALUES immediately above the blanks.

The reason I cant use the above procedure is that the size of the series varies.

Some of them are 5 cells, some are 1, 2, 8 etc.

I really need help with this.


I want to count consecutive values and save each value to a report. So I have hundreds of these, 1 col wide, 48 rows. These contain the word blank and this is what I want to count. I want to count consecutive blanks. So the example below I would have a report that shows me....

Consecutive Blanks with corresponding count value
1 consecutive blanks = 2
2 consecutive blanks = 2
3 consecutive blanks = 2
4 consecutive blanks = 2
5 consecutive blanks = 0
6 consecutive blanks = 0
7 consecutive blanks = 0
8 consecutive blanks = 0

You get the idea....don't have to show 0's. I would want to be able to select multiple sheets within 1 book.


Is there a way to paste special --> Transpose and delete blanks for a
group of data?




A | B | C


I tried paste special --> transpose checked, skip blanks checked which
doesn't work..

Thanks in advance!

Hi, Can anyone help me with this?

I need to copy column B to Column E.

In column B are numbers that are generated from a database and there are blank spaces between them. I would like to copy them to Column E but leave out the blank spaces.

Can this be done with any formula , trick or VBA ?

Thank you very much. Tweek.

I am trying to populate a spreadsheet that has many blanks in one column. I want to use the text in the cell above the blank until the next filled in cell is reached. Then I want to use the text from that cell. I have tried using these instructions, but what is returned is the cell location from above the first blank repeated in each blank cell. I'm trying to fill with text and get the text to change as the already-filled cells change:

1. Select column A.
2. Choose the Edit | Go To... | Special... menu
command, select "Blanks" and press [ OK ].
3. Type the formula, =A2, and press Control+Enter.
4. Select column A once again.
5. Choose the Edit | Copy followed by
Edit | Paste Special... Values.

For step 3, I've tried using the cell name, the up arrow to point to the cell, and moving my cursor to the cell. Nothing has worked. Attached is a snip of what I am getting (note that the rows start with 200, as the first digit has been truncated.)

I'd appreciate any suggestions.


My apologizes if this has already been answered. I looked all over and couldn't find it.

I am using Excel 2008 for a Mac. My goal is to select all blank cells in a section I've highlighted. I know that I can press Control+G, then Command+S to get to the Go To Special page, which provides the ability to select 'Comments', 'Constants', 'Formulas', 'Blanks', and so on. From this window, I don't know how to use my keyboard to move the selection from the default of 'Comments' down to 'Blanks'. I've tried the up and down arrows, pressing Tab, and a bunch of other possible solutions, but I can't find how to get to the 'Blanks' option without using my mouse.

Anyone know how to do this without using a mouse?


I'm trying to highlight all blank cells in a column (to then most likely subsequently delete the entire row ) and have used F5 then find all blanks. This works great unless i perform some other function on the column first. Even if I copy and paste the values (so the blank cells are actually blank instead of a formula that returns blank) it doesn't find any blank cells. Is there a way around this? I don't want to use a macro because I need to eyeball the list before deleting the rows.


I have a Sheet that has Column sequence as follows



I am looking to datasort to group all Blanks - then add a formula; that whn resorted to above layout, the formula populates the code as above - i.e both blanks below Blue will state Blue. Some codes have 2 or 3 blanks whilst others have 50 or 60.

.......or is there a better approach?