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

Giggs1991

New Member
Joined
Mar 17, 2019
Messages
45
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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

aRandomHelper

Active Member
Joined
Jan 14, 2021
Messages
304
Office Version
  1. 2016
Platform
  1. Windows
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
 

Giggs1991

New Member
Joined
Mar 17, 2019
Messages
45
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"
 

aRandomHelper

Active Member
Joined
Jan 14, 2021
Messages
304
Office Version
  1. 2016
Platform
  1. Windows
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
 

Giggs1991

New Member
Joined
Mar 17, 2019
Messages
45

ADVERTISEMENT

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
 

Giggs1991

New Member
Joined
Mar 17, 2019
Messages
45

ADVERTISEMENT

Did you get any errors? or what happened?
I do not get any errors. It deletes everything in the worksheet. Expected result will be to delete only rows below "Work Order"
 

aRandomHelper

Active Member
Joined
Jan 14, 2021
Messages
304
Office Version
  1. 2016
Platform
  1. Windows
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"
 

Giggs1991

New Member
Joined
Mar 17, 2019
Messages
45
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 :)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,072
Messages
5,857,186
Members
431,860
Latest member
Roofing Contractors

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
Top