Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    2

    Unanswered: VB code for Deleting Rows if a condition is not met

    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

  2. #2
    Join Date
    Dec 2003
    Location
    San Diego, CA
    Posts
    153
    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

  3. #3
    Join Date
    Oct 2003
    Posts
    1,091
    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
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •