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

#### Giggs1991

##### New Member
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

##### Board Regular
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
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

##### Board Regular
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

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

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

##### Board Regular
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
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

#### Giggs1991

##### New Member
It work now, thank you
Could you also provide the VBA if I want to delete the column which has the word "Work Order" in it.

Replies
2
Views
60
Replies
0
Views
68
Replies
2
Views
179
Replies
0
Views
86
Replies
4
Views
125

1,127,613
Messages
5,625,848
Members
416,139
Latest member
MattBoard

### 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?

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