Skip rows with particular words

[MacroBB]

Hello all,
I am new to creating Excel macros and needed some help.

Have daily spreadsheets with thousands of lines on each sheet, Each line will contain words like "Cat", "Dog", or "Bird" with dates or value in following 3 columns. Need to remove lines that contain nothing in columns C:E but keeping the "Cat" and "Dog rows even if there is no data in column C:E. See sample below.

	Category	Cash received date	Credit received date	Account balance
1	Cat
2	Dog	Feb 1		$100
3	Bird		Feb 2	$200
4	Cat			$300
5	Dog
6	Bird

The end results, I need to see will be like below.

	Category	Cash received date	Credit received date	Account balance
1	Cat
2	Dog	Feb 1		$100
3	Bird		Feb 2	$200
4	Cat			$300
5	Dog

I did some search and was only able to find this but it can only keep the Cat row.

Dim LR As Long
LR = Cells(Rows.Count, "B").End(xlUp).Row
Range("A1:E" & LR).Select
Selection.AutoFilter
ActiveSheet.Range("A1:E" & LR).AutoFilter Field:=11, Criteria1:=""
ActiveSheet.Range("A1:E" & LR).AutoFilter Field:=6, Criteria1:="Cat"
ActiveSheet.Range("$A$1:$E$" & LR).Offset(1, 0).SpecialCells _
(xlCellTypeVisible).EntireRow.Delete
ActiveSheet.ShowAllData


Can someone please help? Thank you in advance!

 

[Skyybot]

Try this. Test on a copy of you Workbook in case unexpected results occur.

Sub delBlnk()
Dim wb As Workbook, sht As Worksheet, rng1 As Range, rng2 As Range, rng3 As Range, cell As Range, i As Long
Set wb = ThisWorkbook: Set sht = wb.ActiveSheet: Set rng1 = sht.Range(Cells(2, 1), Cells(sht.UsedRange.Rows.Count, 1))
For i = rng1.Rows.Count + 1 To 1 Step -1
    If Not sht.Cells(i, 2).Value = "Cat" Or sht.Cells(i, 1).Value = "Dog" Then
        If IsEmpty(sht.Cells(i, 3)) And IsEmpty(sht.Cells(i, 4)) And IsEmpty(sht.Cells(i, 5)) Then sht.Rows(i).Delete
    End If
Next i
End Sub

 

[Trebor76]

Hi MacroBB,

Here's my attempt which is quite fast (particularly as you have many rows) as any applicable row(s) are not deleted until after all the main procedure:

Option Explicit
Sub Macro1()

    Dim wsSrc As Worksheet
    Dim varArr As Variant
    Dim rngDelete As Range
    Dim i As Long, j As Long, k As Long
    
    Application.ScreenUpdating = False
    
    Set wsSrc = ThisWorkbook.Sheets("Sheet1") 'Sheet name containing the data. Change to suit.
    varArr = Array("Cat", "Dog") 'Category names in Col. B of tab 'wsSrc' to be kept. Amend as required.
    j = 2 'Starting row number
    k = wsSrc.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 'Last row number
    
    For i = j To k
        If IsNumeric(Application.Match(wsSrc.Range("B" & i).Value, varArr, 0)) = False Then
            If WorksheetFunction.CountA(wsSrc.Range("C" & i & ":E" & i)) = 0 Then
                If rngDelete Is Nothing Then
                    Set rngDelete = wsSrc.Range("A" & i)
                Else
                    Set rngDelete = Union(rngDelete, wsSrc.Range("A" & i))
                End If
            End If
        End If
    Next i
    
    If Not rngDelete Is Nothing Then
        rngDelete.EntireRow.Delete
    End If
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[MacroBB]

Try this. Test on a copy of you Workbook in case unexpected results occur.

Sub delBlnk()
Dim wb As Workbook, sht As Worksheet, rng1 As Range, rng2 As Range, rng3 As Range, cell As Range, i As Long
Set wb = ThisWorkbook: Set sht = wb.ActiveSheet: Set rng1 = sht.Range(Cells(2, 1), Cells(sht.UsedRange.Rows.Count, 1))
For i = rng1.Rows.Count + 1 To 1 Step -1
    If Not sht.Cells(i, 2).Value = "Cat" Or sht.Cells(i, 1).Value = "Dog" Then
        If IsEmpty(sht.Cells(i, 3)) And IsEmpty(sht.Cells(i, 4)) And IsEmpty(sht.Cells(i, 5)) Then sht.Rows(i).Delete
    End If
Next i
End Sub

Hi
Thank you for your quick reply but the results only kept 3 rows from the above table that contain data in the last column, did not keep Row 1 & Row 5 from the "expected" result sample.

This is what I got from your codes:

Category	Cash received date	Credit received date	Account balance
Dog	1-Feb​		$100​
Bird		2-Feb​	$200​
Cat			$300​

 

[MacroBB]

Hi MacroBB,

Here's my attempt which is quite fast (particularly as you have many rows) as any applicable row(s) are not deleted until after all the main procedure:

Option Explicit
Sub Macro1()

    Dim wsSrc As Worksheet
    Dim varArr As Variant
    Dim rngDelete As Range
    Dim i As Long, j As Long, k As Long
   
    Application.ScreenUpdating = False
   
    Set wsSrc = ThisWorkbook.Sheets("Sheet1") 'Sheet name containing the data. Change to suit.
    varArr = Array("Cat", "Dog") 'Category names in Col. B of tab 'wsSrc' to be kept. Amend as required.
    j = 2 'Starting row number
    k = wsSrc.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 'Last row number
   
    For i = j To k
        If IsNumeric(Application.Match(wsSrc.Range("B" & i).Value, varArr, 0)) = False Then
            If WorksheetFunction.CountA(wsSrc.Range("C" & i & ":E" & i)) = 0 Then
                If rngDelete Is Nothing Then
                    Set rngDelete = wsSrc.Range("A" & i)
                Else
                    Set rngDelete = Union(rngDelete, wsSrc.Range("A" & i))
                End If
            End If
        End If
    Next i
   
    If Not rngDelete Is Nothing Then
        rngDelete.EntireRow.Delete
    End If
   
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

Hi,
Thank you for your post.
Sorry.... may be I wasn't clear in my original post. I need to keep all the rows that contain "Cat" or "Dog" even if there is no data/amount in the following columns on the same row.

Got the same results as Skybot's with your codes.

Category	Cash received date	Credit received date	Account balance
Dog	1-Feb		$100
Bird		2-Feb	$200
Cat			$300

 

[Trebor76]

Sorry.... may be I wasn't clear in my original post. I need to keep all the rows that contain "Cat" or "Dog" even if there is no data/amount in the following columns on the same row.

Yes that's what the varArr variable is for. It worked for me
My code looks in Col. B for the category - is that where they actually are?

 

[MacroBB]

Yes that's what the varArr variable is for. It worked for me
My code looks in Col. B for the category - is that where they actually are?

Made minor modifications and now working perfectly. Thank you for your help!!