Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete in different worksheets and workbooks
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete in different worksheets and workbooks
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete in different worksheets and workbooks
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete in different worksheets and workbooks
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete in different worksheets and workbooks
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete in different worksheets and workbooks
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy/ move selected data from workbooks to seperate worksheets or workbooks | Excel Worksheet Functions | |||
How to delete duplicates between workbooks? | Excel Worksheet Functions | |||
How to delete duplicates between workbooks? | Excel Worksheet Functions | |||
Simultaneous rst.AddNew and rst.Delete from 2 Workbooks | Excel Programming | |||
Delete columns simultaneously in several different workbooks | Excel Discussion (Misc queries) |