Function to if empty cells present in a column. If yes, delete the corresponding row.

[Shardey]

Sub DelRow()

Sheets("ABCD").Select
Columns("W:W").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete

Sheets("XYZA").Select
Columns("W:W").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete

End Sub

This code helps me to select and delete the corresponding row to every blank cell in the column W for two different sheets. This works great if the column actually has empty cell. (Used a macro recorder for it). I need a test to first check if blank cells are present, only then do this.

 

[Trebor76]

Hi Shardey,

Try this:

Option Explicit
Sub DelBlankRows()

    Dim ws As Worksheet
    Dim varSheet As Variant
    Dim lngLastRow As Long
    
    Application.ScreenUpdating = False
    
    For Each varSheet In Array("ABCD", "XYZA")
        Set ws = ThisWorkbook.Sheets(CStr(varSheet))
        lngLastRow = ws.Cells(Rows.Count, "W").End(xlUp).Row
        If Application.WorksheetFunction.CountIf(ws.Range("W1:W" & lngLastRow), "") > 0 Then
            ws.Range("W1:W" & lngLastRow).SpecialCells(xlCellTypeBlanks).Delete
        End If
        Set ws = Nothing
    Next varSheet
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert