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



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

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


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"STEVEB" wrote in message ...
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



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

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



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

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




--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"STEVEB" wrote in message ...
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



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

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



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

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



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"STEVEB" wrote in message ...
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



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

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)
www.rondebruin.nl



"Ron de Bruin" wrote in message ...
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



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"STEVEB" wrote in message ...
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



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

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

Greg
"Ron de Bruin" wrote in message
...
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)
www.rondebruin.nl



"Ron de Bruin" wrote in message
...
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



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"STEVEB" wrote in message
...
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



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from
http://www.ExcelForum.com/