Delete in different worksheets and workbooks

[Guest]

Hi Experts,

I am trying to repeat the procedure below (delete "Back To Contents" in 45
sheets and 11 workbooks), but I can not find the right code. I am doing with
For next, but is deleting that only in the Active worksheet.

Thanks

Sub deleteBackToContents()

n = Cells(Rows.Count, "A").End(xlUp).Row
For i = n To 1 Step -1
With Cells(i, "A")
If .Value = "Back To Contents" Then
.EntireRow.Delete
End If
End With
Next
End Sub

 

[Guest]

Try this,
If all eleven workbooks are open:

Sub deleteBackToContents()
For Each wb in Workbooks
For Each sh in Worksheets
n = Cells(Rows.Count, "A").End(xlUp).Row
For i = n To 1 Step -1
With Cells(i, "A")
If .Value = "Back To Contents" Then
.EntireRow.Delete
End If
End With
Next
Next sh
Next wb
End Sub

 

[Guest]

Hi JLGWhiz

Thanks for the answer.

I am running your code and still deleting only in the Active worksheet,
nothing happen in the other sheets or workbooks (all opened), any idea why?

Thanks

 

[Guest]

I haven't tested this, but it might solve the problem. Give it a run and let
me know.
It will save me having to set up a test bed.

Sub deleteBackToContents()
For Each wb in Workbooks
For Each sh in Worksheets
With wb.sh
n = .Cells(Rows.Count, "A").End(xlUp).Row
For i = n To 1 Step -1
With .Cells(i, "A")
If .Value = "Back To Contents" Then
.EntireRow.Delete
End If
End With
Next
End With
Next sh
Next wb
End Sub

 

[Guest]

Hi JLHWhiz,

I ran it, an it is giving me a error with: (run time error 438)


With wb.sh

Thanks

 

[Guest]

Some people could do this on the first go round. My brain is getting so
feeble, I'm lucky to remember any of it. See if this cures the 438 problem.

Sub deleteBackToContents()
For Each wb in Workbooks
For Each sh in Worksheets
If Not wb Is Nothing And Not sh Is Nothing Then
a = wb.Name
b = sh.Name
End If
With Workbooks(a).Worksheets(b)
n = .Cells(Rows.Count, "A").End(xlUp).Row
For i = n To 1 Step -1
With .Cells(i, "A")
If .Value = "Back To Contents" Then
.EntireRow.Delete
End If
End With
Next
End With
Next sh
Next wb
End Sub

 

[Guest]

Hi JLHWhiz,

I am getting run time error 9 in

With Workbooks(a).Worksheets(b)

Please let me know if there is any way to fix this
Thanks

 

[Dave Peterson]

I'd try:

Option Explicit
Sub deleteBackToContents()
Dim wb As Workbook
Dim sh As Worksheet
Dim i As Long
Dim n As Long

For Each wb In Workbooks
For Each sh In wb.Worksheets
With sh
n = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = n To 1 Step -1
With .Cells(i, "A")
If LCase(.Value) = LCase("Back To Contents") Then
.EntireRow.Delete
End If
End With
Next i
End With
Next sh
Next wb
End Sub

JLHWhiz had a small bug in this line:
For Each sh in Worksheets
Since Worksheets wasn't qualified, excel thought/knew he meant
For Each sh in Activeworkbook.Worksheets

But that's not what JLHWhiz really wanted.
For Each sh in wb.Worksheets
should be ok.

 

[Guest]

Thanks Dave. I go brain dead after 10:00 pm.

I'd try:

Option Explicit
Sub deleteBackToContents()
Dim wb As Workbook
Dim sh As Worksheet
Dim i As Long
Dim n As Long

For Each wb In Workbooks
For Each sh In wb.Worksheets
With sh
n = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = n To 1 Step -1
With .Cells(i, "A")
If LCase(.Value) = LCase("Back To Contents") Then
.EntireRow.Delete
End If
End With
Next i
End With
Next sh
Next wb
End Sub

JLHWhiz had a small bug in this line:
For Each sh in Worksheets
Since Worksheets wasn't qualified, excel thought/knew he meant
For Each sh in Activeworkbook.Worksheets

But that's not what JLHWhiz really wanted.
For Each sh in wb.Worksheets
should be ok.

 

[Guest]

JLGWhiz and Dave,

Thanks for both of you, this is working PERFECT!!!!!!

Thanks once again