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

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

"Faboboren" wrote:

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

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

"JLGWhiz" wrote:

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

"Faboboren" wrote:

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

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



"Faboboren" wrote:

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

"JLGWhiz" wrote:

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

"Faboboren" wrote:

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

Hi JLHWhiz,

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


With wb.sh

Thanks


"JLGWhiz" wrote:

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



"Faboboren" wrote:

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

"JLGWhiz" wrote:

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

"Faboboren" wrote:

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

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

"Faboboren" wrote:

Hi JLHWhiz,

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


With wb.sh

Thanks


"JLGWhiz" wrote:

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



"Faboboren" wrote:

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

"JLGWhiz" wrote:

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

"Faboboren" wrote:

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

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

"JLGWhiz" wrote:

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

"Faboboren" wrote:

Hi JLHWhiz,

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


With wb.sh

Thanks


"JLGWhiz" wrote:

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



"Faboboren" wrote:

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

"JLGWhiz" wrote:

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

"Faboboren" wrote:

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

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.



Faboboren wrote:

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

"JLGWhiz" wrote:

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

"Faboboren" wrote:

Hi JLHWhiz,

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


With wb.sh

Thanks


"JLGWhiz" wrote:

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



"Faboboren" wrote:

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

"JLGWhiz" wrote:

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

"Faboboren" wrote:

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

--

Dave Peterson

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

"Dave Peterson" wrote:

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.



Faboboren wrote:

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

"JLGWhiz" wrote:

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

"Faboboren" wrote:

Hi JLHWhiz,

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


With wb.sh

Thanks


"JLGWhiz" wrote:

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



"Faboboren" wrote:

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

"JLGWhiz" wrote:

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

"Faboboren" wrote:

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

--

Dave Peterson

JLGWhiz and Dave,

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

Thanks once again

"JLGWhiz" wrote:

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

"Dave Peterson" wrote:

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.



Faboboren wrote:

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

"JLGWhiz" wrote:

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

"Faboboren" wrote:

Hi JLHWhiz,

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


With wb.sh

Thanks


"JLGWhiz" wrote:

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



"Faboboren" wrote:

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

"JLGWhiz" wrote:

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

"Faboboren" wrote:

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

--

Dave Peterson