VB code for Deleting Rows if a condition is not met

mark_s12 · #1 (**permalink**) 04-05-04, 13:44

Is it possible to use VB code to delete entire rows in excel if a condition is not met?

I.e if there is no value in column a, (""), is it possible to delete the entire corresponding row.

i.e if no value in cell a2, delete entire row 2
if no value in cell a4, delete entire row 4

Thanks for your help,

Mark

Smitty · #2 (**permalink**) 04-05-04, 16:53

Welcome to the Board!

If the rows are truly empty and not ="" then you don't need vba.

Select the column

Edit-->Goto-->Special-->Blanks
Edit-->Delete-->Entire Row

If you want a macro then:

Code:

    Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Hope that helps,

Smitty

shades · #3 (**permalink**) 04-05-04, 17:09

If you want variety, this one will allow you to enter the Column letter and use that as the basis for deleting all empty rows (use with caution, if you have partially used cells above your data).

Code:

Sub DeleteEmptyRows()
'This is one I developed to allow user to enter letter
'of the column that is used for deleting.
Application.ScreenUpdating = False
    Dim myColm As String
    Dim n As String
    Dim rng As Range
    Dim lngRow As Long
    myColm = InputBox("Enter Letter")
    
    If myColm <> "" Then
        n = myColm
        Set rng = Range(n & "1", Range(n & "65536").End(xlUp))
        For lngRow = rng.Rows.Count To 2 Step -1
            If rng(lngRow) = "" Then
                rng(lngRow).EntireRow.Delete
            End If
        Next lngRow
    Else
        Exit Sub
    End If
Application.ScreenUpdating = True

End Sub

Internet Services

Web Development

Digital Marketing

Consumer Tech