how to search for blank cells and delete ?

[fluffyvampirekitten]

i want to search for all the blank cells and delete those ?
any idea ?
vba..

thanks in advance

 

[AlexandraT]

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]

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]

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]

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]

are the blanks in any particular column ??

 

[Michael M]

If the blanks are in Col "A", try

Sub MM1()
With Columns("A")
    .AutoFilter field:=1, Criteria1:=""
    .Resize(Rows.Count - 1).Offset(1).EntireRow.Delete
    .AutoFilter
End With
End Sub

 

[fluffyvampirekitten]

If the blanks are in Col "A", try

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

 

[Michael M]

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 ??