Delete Cells in Workbook
 

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/

Delete Cells in Workbook
 

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/

Delete Cells in Workbook
 

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/

Delete Cells in Workbook
 

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/

Delete Cells in Workbook
 

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/

Delete Cells in Workbook
 

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/

Delete Cells in Workbook
 

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/

Delete Cells in Workbook
 

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/

Delete Cells in Workbook
 

Hi Greg

Which excel version you use


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



"Greg Rivet" wrote in message ...
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/

Delete Cells in Workbook
 

Is working for me in 97, 2000 and 2002

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



"Ron de Bruin" wrote in message ...
Hi Greg

Which excel version you use


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



"Greg Rivet" wrote in message ...
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/

Delete Cells in Workbook
 

Ron, Excel XP

Greg
"Ron de Bruin" wrote in message
...
Hi Greg

Which excel version you use


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



"Greg Rivet" wrote in message
...
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/

Delete Cells in Workbook
 

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 ??



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



"Greg Rivet" wrote in message ...
Ron, Excel XP

Greg
"Ron de Bruin" wrote in message
...
Hi Greg

Which excel version you use


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



"Greg Rivet" wrote in message
...
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/