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
44
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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

aRandomHelper

Active Member
Joined
Jan 14, 2021
Messages
278
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
44
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
278
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
44

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
44

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
278
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
44
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 :)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,142
Messages
5,640,367
Members
417,139
Latest member
bdmprasenjit

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