Thread: Delete rows, multiple conditions

Hello,

I need help with the following conditions. I have attached a sample file with desired results.
Conditions required to delete row:

1 Delete Row when cell E is blank (example: Row 6 will be deleted as E6 is blank)
2 First character in column 'C' is an alphabet (for example: Row 5 will be deleted as the first character in the cell is an alphabet
3 First character in column 'B' is an alphabet (for example: Row 29 will be deleted as the first character in the cell is an alphabet
Special Notes : All characters in column 'B' are not in number format (ex: 555-9999 is general)
There are more than 1500 rows of data that need to be sorted with the above conditions

Thanks

Try this:

Code:

Sub x()
    Dim iRow    As Long

    For iRow = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
        If Cells(iRow, "B").Text Like "[A-Z][a-z]*" Or _
           Cells(iRow, "C").Text Like "[A-Z][a-z]*" Or _
           Len(Cells(iRow, "E").Text) = 0 Then
            Rows(iRow).Delete
        End If
    Next iRow
End Sub

rm7302,

Here you go.

shg - nicely done.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Code:

Option Explicit
Sub DeleteRowsOnCondition()
Dim a As Long
Application.ScreenUpdating = False
On Error Resume Next
Range("E2:E" & Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(xlBlanks).EntireRow.Delete
On Error GoTo 0
For a = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
  If Not IsNumeric(Left(Cells(a, 2), 1)) Or Not IsNumeric(Left(Cells(a, 3), 1)) Then
    Rows(a).EntireRow.Delete
  End If
Next a
Application.ScreenUpdating = True
End Sub

Then run the "DeleteRowsOnCondition" macro.

Hello rm7302,

Here is another method. You can change the worksheet name and starting cell if you need to. They are marked in red. The macro will find the last entry in starting column automatically.

Code:

Sub DeleteRows()

  Dim Data As Variant
  Dim Item As Variant
  Dim Rng As Range
  Dim RngEnd As Range
  
    Set Rng = Worksheets("Sheet1").Range("A2")
    Set RngEnd = Rng.Parent.Cells(Rows.Count, Rng.Column).End(xlUp)
    Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, Rng.Parent.Range(Rng, RngEnd))
    
      Data = Rng.Resize(ColumnSize:=5).Value
      
      Application.ScreenUpdating = False
        For I = UBound(Data, 1) To 1 Step -1
          If Data(I, 5) = "" Or Data(I, 3) Like "[a-zA-Z]*" Or Data(I, 2) Like "[a-zA-Z]*" Then
            Rng.Rows(I).EntireRow.Delete
          End If
        Next I
      Application.ScreenUpdating = True
      
End Sub

At a guess you either pasted into the wrong location, or else your sample file doesn't accurately represent your original file.
In what way does it fail, do you receive any error message?

Hello rm7302,

The code needs to be placed in Standard VBA module. Here is how to add the macro to your project.

b]Adding the Macro[/b]
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time.
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.