VBA Loop through all worksheets in a file , find a specific value and delete all rows above that value

Giggs1991

Board Regular
Joined
Mar 17, 2019
Messages
50
Hi All,

I have a work book with the word " Work Order" in many of the worksheets.

I am looking for a VBA code that would loop through all worksheets , find the cell containing the term "work order" and delete all rows above it.


Regards
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Please test on a copy of your workbook first.
VBA Code:
Sub me1158548()
    Dim ws As Worksheet, f As Range
  
    For Each ws In Sheets
        Set f = ws.Cells.Find("Work Order", lookat:=xlWhole)
        If Not f Is Nothing Then
            If f.Row > 1 Then ws.Cells(1).Resize(f.Row - 1).EntireRow.Delete
        End If
    Next
End Sub
 
Upvote 0
Please test on a copy of your workbook first.
VBA Code:
Sub me1158548()
    Dim ws As Worksheet, f As Range
 
    For Each ws In Sheets
        Set f = ws.Cells.Find("Work Order", lookat:=xlWhole)
        If Not f Is Nothing Then
            If f.Row > 1 Then ws.Cells(1).Resize(f.Row - 1).EntireRow.Delete
        End If
    Next
End Sub
Thank you, it worked. Could you please show the code to delete all rows below "Work Order" and all columns to the right and left of "Work Order"
 
Upvote 0
All rows below:
VBA Code:
ws.Cells(f.Row + 1).Resize(Rows.Count - f.Row).EntireRow.Delete
All columns to right:
VBA Code:
ws.Columns(f.Column + 1).Resize(Columns.Count - f.Column).Delete
All columns to left:
VBA Code:
If f.Column > 1 Then ws.Columns(f.Column - 1).Resize(f.Column - 1).Delete
 
Upvote 0
All rows below:
VBA Code:
ws.Cells(f.Row + 1).Resize(Rows.Count - f.Row).EntireRow.Delete
All columns to right:
VBA Code:
ws.Columns(f.Column + 1).Resize(Columns.Count - f.Column).Delete
All columns to left:
VBA Code:
If f.Column > 1 Then ws.Columns(f.Column - 1).Resize(f.Column - 1).Delete
For deleting rows below "work order", I used the following code but it did not work. Am I doing it wrong :

Sub me1158548()
Dim ws As Worksheet, f As Range

For Each ws In Sheets
Set f = ws.Cells.Find("Work Order", lookat:=xlWhole)
If Not f Is Nothing Then
If f.Row > 1 Then ws.Cells(f.Row + 1).Resize(Rows.Count - f.Row).EntireRow.Delete
End If

Next
End Sub
 
Upvote 0
It deletes everything in the worksheet
Apologies for that, try this:
VBA Code:
Sub me1158548()
Dim ws As Worksheet, f As Range

For Each ws In Sheets
Set f = ws.Cells.Find("Work Order", lookat:=xlWhole)
If Not f Is Nothing Then
If f.Row > 1 Then ws.Cells(f.Row + 1, 1).Resize(Rows.Count - f.Row).EntireRow.Delete
End If

Next
End Sub
.Cells was missing a ", 1"
 
Upvote 0
Apologies for that, try this:
VBA Code:
Sub me1158548()
Dim ws As Worksheet, f As Range

For Each ws In Sheets
Set f = ws.Cells.Find("Work Order", lookat:=xlWhole)
If Not f Is Nothing Then
If f.Row > 1 Then ws.Cells(f.Row + 1, 1).Resize(Rows.Count - f.Row).EntireRow.Delete
End If

Next
End Sub
.Cells was missing a ", 1"
It work now, thank you :)
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

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