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

[Giggs1991]

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

 

[aRandomHelper]

Please test on a copy of your workbook first.

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]

Please test on a copy of your workbook first.

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]

All rows below:

ws.Cells(f.Row + 1).Resize(Rows.Count - f.Row).EntireRow.Delete

All columns to right:

ws.Columns(f.Column + 1).Resize(Columns.Count - f.Column).Delete

All columns to left:

If f.Column > 1 Then ws.Columns(f.Column - 1).Resize(f.Column - 1).Delete

 

[Giggs1991]

All rows below:

ws.Cells(f.Row + 1).Resize(Rows.Count - f.Row).EntireRow.Delete

All columns to right:

ws.Columns(f.Column + 1).Resize(Columns.Count - f.Column).Delete

All columns to left:

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

 

[aRandomHelper]

but it did not work

Did you get any errors? or what happened?

 

[Giggs1991]

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]

It deletes everything in the worksheet

Apologies for that, try this:

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]

Apologies for that, try this:

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]

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.