Delete Cells in Workbook

  • Thread starter Thread starter STEVEB
  • Start date Start date
S

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




Ron de Bruin said:
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
Click to expand...
 
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 ??
 
Back
Top