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
 

aRandomHelper

Active Member
Joined
Jan 14, 2021
Messages
278
Office Version
  1. 2016
Platform
  1. Windows
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
            f.EntireColumn.Delete
        End If
    
    Next
End Sub
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Giggs1991

New Member
Joined
Mar 17, 2019
Messages
44
thank you so much.
Is it possible to get vba to select range A1 to BZ1 in all worksheets and change format to "mmm-yy" , change background colour in this range to black and make font bold with white font colour.
 

aRandomHelper

Active Member
Joined
Jan 14, 2021
Messages
278
Office Version
  1. 2016
Platform
  1. Windows
Yes it's possible, but it is preferable that if you have any new questions, they be asked in a new thread.

Here's what you need:
VBA Code:
    For Each ws In Sheets
        With ws.Range("A1:BZ1")
            .NumberFormat = "mmm-yy"
            .Interior.Color = 0
            .Font.Color = vbWhite
        End With
    Next
 

Giggs1991

New Member
Joined
Mar 17, 2019
Messages
44
Yes it's possible, but it is preferable that if you have any new questions, they be asked in a new thread.

Here's what you need:
VBA Code:
    For Each ws In Sheets
        With ws.Range("A1:BZ1")
            .NumberFormat = "mmm-yy"
            .Interior.Color = 0
            .Font.Color = vbWhite
        End With
    Next
This one also works well. I had another question regarding looping through worksheets. As requested, I have posted it as a new thread here : VBA Loop through all worksheets in a file , find a specific value and update the cell to the right

Please help if possible.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,146
Messages
5,640,395
Members
417,140
Latest member
whiteprose

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