how to search for blank cells and delete ?

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

AlexandraT

Board Regular
Joined
Mar 23, 2015
Messages
144
Not sure if you prefer vba or not but here is the non-vba method. Select the rows/columns where you want to search for blanks. Press F5, click Special and select blanks. This will automatically select all blanks in the selected area. These can then be deleted in the HOME tab ->Delete.
Alex
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,766
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Blank cells where ??
AND
Do you only want to delete the cell or the row ??
By deleting blank cells within a range of data, any adjacent data will be incorrect once the blank cell is deleted ??
 

fluffyvampirekitten

Board Regular
Joined
Jul 1, 2015
Messages
72
Not sure if you prefer vba or not but here is the non-vba method. Select the rows/columns where you want to search for blanks. Press F5, click Special and select blanks. This will automatically select all blanks in the selected area. These can then be deleted in the HOME tab ->Delete.
Alex


I prefer vba.. running the macro ..
 

fluffyvampirekitten

Board Regular
Joined
Jul 1, 2015
Messages
72

ADVERTISEMENT

Blank cells where ??
I have a list of data like around thousands , most of the rows are blank and its in between two data.
Idea: I want to delete all the blank rows and do a filter ( A to Z)


AND
Do you only want to delete the cell or the row ?? I want to delete the row.
By deleting blank cells within a range of data, any adjacent data will be incorrect once the blank cell is deleted ??

Answers in red
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,766
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
are the blanks in any particular column ??
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,766
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

If the blanks are in Col "A", try
Code:
Sub MM1()
With Columns("A")
    .AutoFilter field:=1, Criteria1:=""
    .Resize(Rows.Count - 1).Offset(1).EntireRow.Delete
    .AutoFilter
End With
End Sub
 

fluffyvampirekitten

Board Regular
Joined
Jul 1, 2015
Messages
72
If the blanks are in Col "A", try
Code:
Sub MM1()
With Columns("A")
    .AutoFilter field:=1, Criteria1:=""
    .Resize(Rows.Count - 1).Offset(1).EntireRow.Delete
    .AutoFilter
End With
End Sub


Actually its in a random places.
no particular place .

The file is generated from the system thus it is not stardardized
 
Last edited:

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,766
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
So, if the blanks are random, and you want the rows deleted, won't you be losing data in any other cells in the deleted row ??
 

Watch MrExcel Video

Forum statistics

Threads
1,123,290
Messages
5,600,759
Members
414,405
Latest member
Zaurb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top