How to replace blank cells with N/A

**brogart28** · 02-21-2020, 07:38 AM

Hi All

So I have a report which contains rows of blank cells in between rows that have data. However, in between some of the cells on the rows that has data are also blank cells. I would ideally like to remove ONLY the blank rows and replace any cells that are blank on the rows with data with N/A.

At the moment I am currently highlighing all the blank rows I do this through - GO TO - Special and clicking on the blanks to highlight them) Once the blank rows are highlighted, I paint them a colour and then go through the spread sheet looking for cells that are blank on the rows that have data and manually enter N/A in these.

Once I put N/A in any cells on rows that have data I then delete all the blank rows.

Does anyone have any quick way of entering N/A in one quick go and then allowing me to just delete all the blank rows?

Hope this makes sense and thanks in advance for reading this post and for any help

**mehmetcik** · 02-21-2020, 08:43 AM

You will need a Macro.

Insert a formula in an empty column to Flag all empty rows.

Select All your flagged Rows and Delete them.

Select All Blank Cells

Replace the blanks with #NA

Please Login or Register  to view this content.

**Imran045** · 02-21-2020, 09:15 AM

You can delete the blank rows firts.

Then Go to Special > Select Blanks > Click ok

Once the blank cells are selected. Enter N/A without selecting any cell and Press Ctrl + Enter.

All the blank cells will be replaced with N/A.

**brogart28** · 02-21-2020, 09:34 AM

Thanks for your help guys - unfortunately as you can see from the attachment. I am able to select Go to Special > Select Blanks > Click ok and then i highlight the blank cells with a different coour. However as you can see from column F row 458 I also have a blank cell which I want to keep and put N/A into..... If I delete all blanks this deletes too.....

Test A Test A Test A Test A Test A Test A Test A Test A N/A N/A N/A N/A N/A N/A N/A

Test A Test A Test A Test A Test A Test A Test A Test A 48 15 2 N/A 13 219 398 N/A

**brogart28** · 02-21-2020, 09:35 AM

Thanks for your help guys - unfortunately as you can see from the attachment. I am able to select Go to Special > Select Blanks > Click ok and then i highlight the blank cells with a different coour. However as you can see from column F row 458 I also have a blank cell which I want to keep and put N/A into..... If I delete all blanks this deletes too.....