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

Board Regular
Joined
Jan 14, 2021
Messages
245
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
 

Some videos you may like

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.

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

Board Regular
Joined
Jan 14, 2021
Messages
245
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,128,165
Messages
5,629,068
Members
416,363
Latest member
zaveedd

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