CODEWITHSUNDEEP
excelvba
user8422137
user8422137

Reputation:

To run the code further if no blank cells are found in a selected range

I wrote a code to delete the row itself if any of the cell is blank in a selected range but its getting terminated if no blanks are found. i want to run the code further. Here is the code:- 

Application.ScreenUpdating = False 
Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Application.ScreenUpdating = True 

'For instance if i want the machine to pop up a msgbox,in case if no blanks found   

Msgbox "Congrats!"

Upvotes: 0

Views: 104

Answers (1)

Scott Craner
Scott Craner

Reputation: 152465

Test whether the Count of cells with values is the same as the number of cells in the range:

Application.ScreenUpdating = False
With ActiveSheet 'Better practice to assign actual sheet.
    If Not Application.WorksheetFunction.CountA(.Range("A:A")) = .Range("A:A").Cells.Count Then
        .Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    Else
        MsgBox "Congrats!"
    End If
End With
Application.ScreenUpdating = True

Upvotes: 1

Related Questions