VBA Help-Delete Row if criteria met

A

AussieVic

Active Member
Joined
Jan 9, 2009
Messages
364
Hi I have 3 worksheets ("Citi",JPM",BONY") , i need a VBA code that will look in 3 of the worksheets, if string "CLEARED" found in Col U5:U in sheet ("Citi"), Col S5:S in sheet ("JPM") and Col R5:R in Sheet ("BONY") then delete entire row. There will be blank cells which are required in these columns, therefore autofilter cannot be used since i have data starting from A5.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
Hi Aussie,

As the following will delete rows, try it on a copy of your workbook in case the results are not as expected. Note also the reference to Rick Rothstein's "Delete Row With Specific Word" macro which I've incorporated below to (hopefully) achieve your desired result:

Code: 
'http://www.mrexcel.com/forum/newreply.php?do=newreply&noquote=1&p=2809395
Sub Macro1()
    
    Dim sWord As String
           
    sWord = "CLEARED"

    Application.ScreenUpdating = False
    
    For Each Worksheet In ThisWorkbook.Sheets
           
        Select Case Worksheet.Name
        
            Case Is = "Citi"
        
                With Sheets("Citi").Range("U5", Range("U" & Rows.Count).End(xlUp))
                    .Replace "", "z"
                    .Replace sWord, "#N/A", xlWhole 'Based on Rick Rothstein's very clever Delete Row With Specific Word from here: http://www.contextures.com/rickrothsteinexcelvbatext.html
                        On Error Resume Next ' Account for no cells being found.
                            .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
                        On Error GoTo 0
                    .Replace "z", ""
                End With
                
            Case Is = "JPM"
        
                With Sheets("JPM").Range("S5", Range("S" & Rows.Count).End(xlUp))
                    .Replace "", "z"
                    .Replace sWord, "#N/A", xlWhole 'Based on Rick Rothstein's very clever Delete Row With Specific Word from here: http://www.contextures.com/rickrothsteinexcelvbatext.html
                        On Error Resume Next ' Account for no cells being found.
                            .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
                        On Error GoTo 0
                    .Replace "z", ""
                End With
                
                
            Case Is = "BONY"
        
                With Sheets("BONY").Range("R5", Range("R" & Rows.Count).End(xlUp))
                    .Replace "", "z"
                    .Replace sWord, "#N/A", xlWhole 'Based on Rick Rothstein's very clever Delete Row With Specific Word from here: http://www.contextures.com/rickrothsteinexcelvbatext.html
                        On Error Resume Next ' Account for no cells being found.
                            .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
                        On Error GoTo 0
                    .Replace "z", ""
                End With
                
        End Select
        
    Next Worksheet
    
    Application.ScreenUpdating = True

End Sub

HTH

Robert
 
Upvote 0
Trebor76 said:
Code: 
Case Is = "Citi"
    With Sheets("Citi").Range("U5", Range("U" & Rows.Count).End(xlUp))
        .Replace "", "z"
        .Replace sWord, "#N/A", xlWhole 'Based on Rick Rothstein's very clever Delete Row With Specific Word from here: [URL]http://www.contextures.com/rickrothsteinexcelvbatext.html[/URL]
            On Error Resume Next ' Account for no cells being found.
                .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
            On Error GoTo 0
        .Replace "z", ""
    End With
Click to expand...
What is the purpose of replacing empty cells with "z" and then setting them back again at the end?

Trebor76 said:
Code: 
'Based on Rick Rothstein's very clever Delete Row With Specific Word from here: [URL]http://www.contextures.com/rickrothsteinexcelvbatext.html[/URL]
Click to expand...
While I appreciate the acknowledgement, it is not necesary for you to do so (unless Deb has some kind of over-riding copyright on code by others that she posts on her website, which I do not believe is the case). I've posted that code, and variations of it all over the web, so it is out there for anyone to use in any manner they see fit. Besides, I have trouble believing that I "invented" that approach in the first place... I would expect if you could develop the correct Google search criteria, that you would find others who have posted similar code prior to my discovery of the approach.
 
Upvote 0
Hi Robert,

When i run the code it fails on this line, run time error 1004, application defined obect error, it works on "Citi" if i run the code whilst the active sheet is Citi but fails on JPM if its not active. I will be running the code from a separate worksheet

With Sheets("JPM").Range("S5", Range("S" & Rows.Count).End(xlUp))





Trebor76 said:
Hi Aussie,

As the following will delete rows, try it on a copy of your workbook in case the results are not as expected. Note also the reference to Rick Rothstein's "Delete Row With Specific Word" macro which I've incorporated below to (hopefully) achieve your desired result:

Code: 
'http://www.mrexcel.com/forum/newreply.php?do=newreply&noquote=1&p=2809395
Sub Macro1()
    
    Dim sWord As String
           
    sWord = "CLEARED"

    Application.ScreenUpdating = False
    
    For Each Worksheet In ThisWorkbook.Sheets
           
        Select Case Worksheet.Name
        
            Case Is = "Citi"
        
                With Sheets("Citi").Range("U5", Range("U" & Rows.Count).End(xlUp))
                    .Replace "", "z"
                    .Replace sWord, "#N/A", xlWhole 'Based on Rick Rothstein's very clever Delete Row With Specific Word from here: http://www.contextures.com/rickrothsteinexcelvbatext.html
                        On Error Resume Next ' Account for no cells being found.
                            .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
                        On Error GoTo 0
                    .Replace "z", ""
                End With
                
            Case Is = "JPM"
        
                With Sheets("JPM").Range("S5", Range("S" & Rows.Count).End(xlUp))
                    .Replace "", "z"
                    .Replace sWord, "#N/A", xlWhole 'Based on Rick Rothstein's very clever Delete Row With Specific Word from here: http://www.contextures.com/rickrothsteinexcelvbatext.html
                        On Error Resume Next ' Account for no cells being found.
                            .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
                        On Error GoTo 0
                    .Replace "z", ""
                End With
                
                
            Case Is = "BONY"
        
                With Sheets("BONY").Range("R5", Range("R" & Rows.Count).End(xlUp))
                    .Replace "", "z"
                    .Replace sWord, "#N/A", xlWhole 'Based on Rick Rothstein's very clever Delete Row With Specific Word from here: http://www.contextures.com/rickrothsteinexcelvbatext.html
                        On Error Resume Next ' Account for no cells being found.
                            .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
                        On Error GoTo 0
                    .Replace "z", ""
                End With
                
        End Select
        
    Next Worksheet
    
    Application.ScreenUpdating = True

End Sub
HTH

Robert
Click to expand...
 
Upvote 0
Hi Rick,

When I orginally tested the code I thought it deleted blank cells as well as error cells so I did this as a "work around". I have seen on later runs that it's not necessary so I've removed it on the following.

I always try and praise other people's clever work which I have also done for you on another post. I'll keep it in mind for future posts.

Hi AussieVic,

Hopefully the following will do the trick.

HTH

Robert

Code: 
'http://www.mrexcel.com/forum/newreply.php?do=newreply&noquote=1&p=2809395
Sub Macro1()
    
    Dim sWord As String
           
    sWord = "CLEARED"

    Application.ScreenUpdating = False
    
    For Each Worksheet In ThisWorkbook.Sheets
           
        Select Case Worksheet.Name
        
            Case Is = "Citi"
        
                With Sheets("Citi")
                    .Range("U5", .Range("U" & Rows.Count).End(xlUp)).Replace sWord, "#N/A", xlWhole 'Based on Rick Rothstein's very clever Delete Row With Specific Word from here: http://www.contextures.com/rickrothsteinexcelvbatext.html
                        On Error Resume Next ' Account for no cells being found.
                            .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
                        On Error GoTo 0
                End With
                
            Case Is = "JPM"
        
                With Sheets("JPM")
                    .Range("S5", .Range("S" & Rows.Count).End(xlUp)).Replace sWord, "#N/A", xlWhole 'Based on Rick Rothstein's very clever Delete Row With Specific Word from here: http://www.contextures.com/rickrothsteinexcelvbatext.html
                        On Error Resume Next ' Account for no cells being found.
                            .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
                        On Error GoTo 0
                End With
                
                
            Case Is = "BONY"
        
                With Sheets("BONY")
                    .Range("R5", .Range("R" & Rows.Count).End(xlUp)).Replace sWord, "#N/A", xlWhole 'Based on Rick Rothstein's very clever Delete Row With Specific Word from here: http://www.contextures.com/rickrothsteinexcelvbatext.html
                        On Error Resume Next ' Account for no cells being found.
                            .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
                        On Error GoTo 0
                End With
                
        End Select
        
    Next Worksheet
    
    Application.ScreenUpdating = True

End Sub
 
Upvote 0
Nah that did not work, the code replaced with #N/A but did not delete entire row


Trebor76 said:
Hi Rick,

When I orginally tested the code I thought it deleted blank cells as well as error cells so I did this as a "work around". I have seen on later runs that it's not necessary so I've removed it on the following.

I always try and praise other people's clever work which I have also done for you on another post. I'll keep it in mind for future posts.

Hi AussieVic,

Hopefully the following will do the trick.

HTH

Robert

Code: 
'http://www.mrexcel.com/forum/newreply.php?do=newreply&noquote=1&p=2809395
Sub Macro1()
    
    Dim sWord As String
           
    sWord = "CLEARED"

    Application.ScreenUpdating = False
    
    For Each Worksheet In ThisWorkbook.Sheets
           
        Select Case Worksheet.Name
        
            Case Is = "Citi"
        
                With Sheets("Citi")
                    .Range("U5", .Range("U" & Rows.Count).End(xlUp)).Replace sWord, "#N/A", xlWhole 'Based on Rick Rothstein's very clever Delete Row With Specific Word from here: http://www.contextures.com/rickrothsteinexcelvbatext.html
                        On Error Resume Next ' Account for no cells being found.
                            .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
                        On Error GoTo 0
                End With
                
            Case Is = "JPM"
        
                With Sheets("JPM")
                    .Range("S5", .Range("S" & Rows.Count).End(xlUp)).Replace sWord, "#N/A", xlWhole 'Based on Rick Rothstein's very clever Delete Row With Specific Word from here: http://www.contextures.com/rickrothsteinexcelvbatext.html
                        On Error Resume Next ' Account for no cells being found.
                            .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
                        On Error GoTo 0
                End With
                
                
            Case Is = "BONY"
        
                With Sheets("BONY")
                    .Range("R5", .Range("R" & Rows.Count).End(xlUp)).Replace sWord, "#N/A", xlWhole 'Based on Rick Rothstein's very clever Delete Row With Specific Word from here: http://www.contextures.com/rickrothsteinexcelvbatext.html
                        On Error Resume Next ' Account for no cells being found.
                            .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
                        On Error GoTo 0
                End With
                
        End Select
        
    Next Worksheet
    
    Application.ScreenUpdating = True

End Sub
Click to expand...
 
Upvote 0
One more try for the dummies:

Code: 
'http://www.mrexcel.com/forum/newreply.php?do=newreply&noquote=1&p=2809395
Sub Macro1()
    
    Dim sWord As String
           
    sWord = "CLEARED"

    Application.ScreenUpdating = False
    
    For Each Worksheet In ThisWorkbook.Sheets
           
        Select Case Worksheet.Name
        
            Case Is = "Citi"
        
                With Sheets("Citi")
                    With .Range("U5", .Range("U" & Rows.Count).End(xlUp))
                        .Replace sWord, "#N/A", xlWhole 'Based on Rick Rothstein's very clever Delete Row With Specific Word from here: http://www.contextures.com/rickrothsteinexcelvbatext.html
                        On Error Resume Next ' Account for no cells being found.
                            .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
                        On Error GoTo 0
                    End With
                End With
                
            Case Is = "JPM"
        
                With Sheets("JPM")
                    With .Range("S5", .Range("S" & Rows.Count).End(xlUp))
                        .Replace sWord, "#N/A", xlWhole 'Based on Rick Rothstein's very clever Delete Row With Specific Word from here: http://www.contextures.com/rickrothsteinexcelvbatext.html
                        On Error Resume Next ' Account for no cells being found.
                            .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
                        On Error GoTo 0
                    End With
                End With
                
                
            Case Is = "BONY"
        
                With Sheets("BONY")
                    With .Range("R5", .Range("R" & Rows.Count).End(xlUp))
                        .Replace sWord, "#N/A", xlWhole 'Based on Rick Rothstein's very clever Delete Row With Specific Word from here: http://www.contextures.com/rickrothsteinexcelvbatext.html
                        On Error Resume Next ' Account for no cells being found.
                            .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
                        On Error GoTo 0
                    End With
                End With
                
        End Select
        
    Next Worksheet
    
    Application.ScreenUpdating = True

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
Adding to an existing MACRO
Replies
2
Views
119
Northern NY Design
N
L
  • Question Question
VBA question
Replies
6
Views
110
Skyybot
Skyybot
I
  • Solved
Large file applying formulas via VBA help
Replies
9
Views
518
ItalianPlatinum
I
T
  • Solved
Find #REF! errors in multiple tables and delete those rows
Replies
7
Views
305
Peter_SSs
Peter_SSs
S
  • Question Question
error on deleting last row
Replies
5
Views
364
Anthony47
A

Forum statistics

Threads
1,224,574
Messages
6,179,626
Members
452,933
Latest member
patv

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top