Creating a Macro/Script where it automatically deletes All Rows With a Blank Cell

[deejay1234]

Hi,

I am trying to create a macro or a script where it automatically deletes all rows that has a blank cell. When recording the script on Excel it recognizes the steps that I am doing but when I use it on a different sheet it doesnt do it properly. Please help.

Name	Location	Sale
Aaron	USA	210
Bianca	Canada
Calvin		310

From the table above, I want to remove the 2nd and 3rd rows since it has a blank cell. So in this example when I run the Macro or the script it should only return as the 1st row.

Thanks,

 

[Joe4]

Which columns exactly do you want to check?
Please provide the letters (is it just columns A-C, or are there more than that)?

 

[Joe4]

If it is only columns A-C, then this code should work:

Sub MyDeleteRows()

    Dim lr As Long
    Dim r As Long
    
    Application.ScreenUpdating = False

'   Find last row on sheet with data
    lr = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
'   Loop through all rows backwards
    For r = lr To 2 Step -1
'       If all columns are not populated, delete row
        If Application.WorksheetFunction.CountA(Range(Cells(r, "A"), Cells(r, "C"))) < 3 Then Rows(r).Delete
    Next r

    Application.ScreenUpdating = True
    
End Sub

 

[deejay1234]

If it is only columns A-C, then this code should work:

Sub MyDeleteRows()

    Dim lr As Long
    Dim r As Long
   
    Application.ScreenUpdating = False

'   Find last row on sheet with data
    lr = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
   
'   Loop through all rows backwards
    For r = lr To 2 Step -1
'       If all columns are not populated, delete row
        If Application.WorksheetFunction.CountA(Range(Cells(r, "A"), Cells(r, "C"))) < 3 Then Rows(r).Delete
    Next r

    Application.ScreenUpdating = True
   
End Sub

Hi Joe,

What if the Columns got bigger to say A-L , should I just then change this "If Application.WorksheetFunction.CountA(Range(Cells(r, "A"), Cells(r, "C"))) < 3 Then Rows(r).Delete" to
"If Application.WorksheetFunction.CountA(Range(Cells(r, "A"), Cells(r, "L"))) < 3 Then Rows(r).Delete"

THanks?

 

[Joe4]

You would need to change the "C" to an "L", and change the 3 to 12, as that means that there are 12 columns, not 3.
Basically, we are counting the number of populated cells in column A:L, and want to see if it is less than 12.
If it is, it means that there is at least one blank column.