Need Help Deleting Rows on all sheets (Quick) (VBA/Macro)

[joshmorris23xx]

Hi all,

Looking for a macro to achieve the following.

In column AA of a sheet named "Welcome" no quotes, I have various text in its cells.
If the cell does not contain the word "facts" no quotes in it, I would like to have that row deleted from ALL sheets in the workbook, and not leave any white empty space.

For example, cell AA1 says "facts hi", since it contains "facts", Row 1 should remain on all sheets.
Cell AA2 says "bye", since it does not contain facts, Row 2 should be entirely deleted from all sheets in the workbook.


All help appreciated getting this done!

 

[Michael M]

So, you only want to search "Welcome" sheet for the text...then remove the row from ALL sheets ?
Will the rows in other sheets have the same text ??

 

[Trebor76]

Hi joshmorris23xx,

Welcome to MrExcel!!

Try this (though initially on a copy of your data as the results cannot be undone if they're not as expected):

Option Explicit
Sub Macro1()
    
    Dim lngLastRow As Long
    Dim lngMyRow As Long
    Dim wsMySheet As Worksheet
      
    Application.ScreenUpdating = False
    
    lngLastRow = Sheets("Welcome").Cells(Rows.Count, "AA").End(xlUp).Row
    
    For lngMyRow = lngLastRow To 1 Step -1 'When deleting rows it's best to work backwards through the rows
        If IsError(Evaluate("SEARCH(""facts"",Welcome!AA" & lngMyRow & ")")) Then
            'The following three lines are sourced from here: _
            https://www.extendoffice.com/documents/excel/3641-excel-delete-rows-across-multiple-sheets.html
            ThisWorkbook.Worksheets.Select
            Rows(lngMyRow).Select
            Selection.Delete
        End If
    Next lngMyRow
    
    Sheets("Welcome").Select
    Range("AA1").Select
    
    Application.ScreenUpdating = True
    
    MsgBox "Process complete.", vbInformation
 
End Sub

Regards,

Robert

 

[Michael M]

Hi Rob
I'll toss in mine as well

Sub MM1()
Dim r As Long, lr As Long, ws As Worksheet
Application.ScreenUpdating = False
Set wel = Sheets("Welcome")
lr = wel.Cells(Rows.Count, "AA").End(xlUp).Row
For r = lr To 2 Step -1
    If InStr(Range("AA" & r), "facts") = 0 Then
        For Each ws In Worksheets
            ws.Activate
            Rows(r).Delete
        Next ws
    wel.Activate
    End If
Next r
Application.ScreenUpdating = True
End Sub

 

[Trebor76]

Hey Michael,

It's been a while. Hope all is good with you and yours.

Balmy windy winter's here in Sydney. Is the same with you?

 

[joshmorris23xx]

Thanks! this worked great!

Just one thing that I forgot to mention: Can you make it exclude Row 1? It contains some headers that are important.

Hi joshmorris23xx,

Welcome to MrExcel!!

Try this (though initially on a copy of your data as the results cannot be undone if they're not as expected):

Option Explicit
Sub Macro1()
    
    Dim lngLastRow As Long
    Dim lngMyRow As Long
    Dim wsMySheet As Worksheet
      
    Application.ScreenUpdating = False
    
    lngLastRow = Sheets("Welcome").Cells(Rows.Count, "AA").End(xlUp).Row
    
    For lngMyRow = lngLastRow To 1 Step -1 'When deleting rows it's best to work backwards through the rows
        If IsError(Evaluate("SEARCH(""facts"",Welcome!AA" & lngMyRow & ")")) Then
            'The following three lines are sourced from here: _
            https://www.extendoffice.com/documents/excel/3641-excel-delete-rows-across-multiple-sheets.html
            ThisWorkbook.Worksheets.Select
            Rows(lngMyRow).Select
            Selection.Delete
        End If
    Next lngMyRow
    
    Sheets("Welcome").Select
    Range("AA1").Select
    
    Application.ScreenUpdating = True
    
    MsgBox "Process complete.", vbInformation
 
End Sub

Regards,

Robert

 

[Michael M]

mine any good....doesn't delete row 1

 

[Michael M]

Hey Robert
Haven't been on the forum much of late....kinda retired from Excel AND work ....play plenty of golf though!
Cold out west and really dry....but it seems to be the norm anywhere east of Alice these days !!!

 

[Trebor76]

Can you make it exclude Row 1?

I included Row 1 as your original post said to. To set what row the code will check up to and including, change the bold 1 in the following line of code (presumably to 2):

For lngMyRow = lngLastRow To [B]1[/B] Step -1 'When deleting rows it's best to work backwards through the rows

HTH

Robert

 

[Trebor76]

Hi Michael,

Same here. I started a new job recently so haven't been on the forum much either. Yes, it's certainly been been dry. I feel for the farmers.

Take care,

Robert