Delete Cells in Workbook

[STEVEB]

I have a workbook that has approximately 20 worksheets. I would like a
Macro to run and delete all cells in each of the worksheets that have
"Saturday" in column A. Does anyone have any ideas.

Thanks

 

[Ron de Bruin]

Do you really want to delete them or clear them??
Try this

Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
sh.Columns(1).Replace What:="Saturday", Replacement:=""
Next

 

[STEVEB]

Hi Ron,

Thanks for your response, I would actually like to delete the entire
row from each worksheet that contians "Saturday" in column A.

Steve

 

[Ron de Bruin]

Try this on a test workbook

Sub test()
Dim sh As Worksheet
Dim rng As Range
Dim rng2 As Range
Dim findstring As String

findstring = "Saturday"
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
sh.Select
sh.UsedRange.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=findstring
Set rng = ActiveSheet.AutoFilter.Range
Set rng2 = rng.Range("a2:a" & rng.Rows.Count).SpecialCells(xlVisible)
rng2.EntireRow.Delete
Selection.AutoFilter
Next
Application.ScreenUpdating = True
End Sub

 

[STEVEB]

Thanks Ron,

When I run the code that you suggested, I get the following error: On
this line: sh.Select

Run time error '1004'
Method 'Select' of object'_Worksheet'failed

Is the worksheets are named rather than Sheet 1, Sheet 2, etc.

Thanks for you help,

Steve

 

[Ron de Bruin]

Strange !!

This line must work
sh.Select


I only add a on error to the sub(see below)
for if the word not exist.

You can mail me your workbook so that I can look if you want

Sub test2()
Dim sh As Worksheet
Dim rng As Range
Dim rng2 As Range
Dim findstring As String

findstring = "Saturday"
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
sh.Select
sh.UsedRange.AutoFilter
On Error Resume Next
Selection.AutoFilter Field:=1, Criteria1:=findstring
Set rng = ActiveSheet.AutoFilter.Range
Set rng2 = rng.Range("a2:a" & rng.Rows.Count).SpecialCells(xlVisible)
rng2.EntireRow.Delete
Selection.AutoFilter
On Error GoTo 0
Next
Application.ScreenUpdating = True
End Sub

 

[Ron de Bruin]

After thinking about it I think you have a hidden sheet
Am I right?

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)

Strange !!

This line must work
sh.Select


I only add a on error to the sub(see below)
for if the word not exist.

You can mail me your workbook so that I can look if you want

Sub test2()
Dim sh As Worksheet
Dim rng As Range
Dim rng2 As Range
Dim findstring As String

findstring = "Saturday"
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
sh.Select
sh.UsedRange.AutoFilter
On Error Resume Next
Selection.AutoFilter Field:=1, Criteria1:=findstring
Set rng = ActiveSheet.AutoFilter.Range
Set rng2 = rng.Range("a2:a" & rng.Rows.Count).SpecialCells(xlVisible)
rng2.EntireRow.Delete
Selection.AutoFilter
On Error GoTo 0
Next
Application.ScreenUpdating = True
End Sub

 

[Greg Rivet]

Ron, I get the same error with no hidden sheet.

Greg

After thinking about it I think you have a hidden sheet
Am I right?

 

[Ron de Bruin]

Hi Greg

Which excel version you use

 

[Ron de Bruin]

Is working for me in 97, 2000 and 2002

 

[Greg Rivet]

Ron, Excel XP

Greg

Hi Greg

Which excel version you use

 

[Ron de Bruin]

Hi Greg

Steve get the error on the sh.select line

This will also not work for him

Sub test()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
sh.Select
MsgBox sh.Name
Next
End Sub

Is this working correct for you ??