Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Only works on One Worksheet, When Looping thru All WS
I am trying to get the operations listed inside the first
For loop to work on each Worksheet as it goes thru each one. I know it steps thru them, I've used a msgbox to show each ones name. But it will only perform the requested operations on ONE worksheet. What am I doing wrong? Thx for your help - Dean Sub DeleteAllNoneDataRowsx() 'This will only work on One Tab at a time ' Dim wks As Worksheet Dim r As Integer For Each wks In Worksheets LastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count Application.ScreenUpdating = False For r = LastRow To 1 Step -1 If Application.WorksheetFunction.CountA (Rows(r)) = 0 _ Then Rows(r).Delete Else If ActiveSheet.Cells(r, 15).Value = "Total" _ Then Rows(r).Delete Else If ActiveSheet.Cells(r, 15).Value = "Value" _ Then Rows(r).Delete Else If ActiveSheet.Cells(r, 15).Value = "" _ Then Rows(r).Delete Next r Next wks End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Only works on One Worksheet, When Looping thru All WS
Hi Dean
You use activesheet and this is always the same sheet in your code One way isto add a select line in the loop For Each wks In Worksheets wks.Select Or change you code so that it will use the reference to the sheet in the loop This is better and faster -- Regards Ron de Bruin http://www.rondebruin.nl "Dean" wrote in message ... I am trying to get the operations listed inside the first For loop to work on each Worksheet as it goes thru each one. I know it steps thru them, I've used a msgbox to show each ones name. But it will only perform the requested operations on ONE worksheet. What am I doing wrong? Thx for your help - Dean Sub DeleteAllNoneDataRowsx() 'This will only work on One Tab at a time ' Dim wks As Worksheet Dim r As Integer For Each wks In Worksheets LastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count Application.ScreenUpdating = False For r = LastRow To 1 Step -1 If Application.WorksheetFunction.CountA (Rows(r)) = 0 _ Then Rows(r).Delete Else If ActiveSheet.Cells(r, 15).Value = "Total" _ Then Rows(r).Delete Else If ActiveSheet.Cells(r, 15).Value = "Value" _ Then Rows(r).Delete Else If ActiveSheet.Cells(r, 15).Value = "" _ Then Rows(r).Delete Next r Next wks End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Only works on One Worksheet, When Looping thru All WS
Ron,
You have fixed it in one step. I had tried earlier using Worksheets (wks).Select, but that didn't do it. Using your wks.Select it worked the first time. Thanks very much. Dean -----Original Message----- Hi Dean You use activesheet and this is always the same sheet in your code One way isto add a select line in the loop For Each wks In Worksheets wks.Select Or change you code so that it will use the reference to the sheet in the loop This is better and faster -- Regards Ron de Bruin http://www.rondebruin.nl "Dean" wrote in message ... I am trying to get the operations listed inside the first For loop to work on each Worksheet as it goes thru each one. I know it steps thru them, I've used a msgbox to show each ones name. But it will only perform the requested operations on ONE worksheet. What am I doing wrong? Thx for your help - Dean Sub DeleteAllNoneDataRowsx() 'This will only work on One Tab at a time ' Dim wks As Worksheet Dim r As Integer For Each wks In Worksheets LastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count Application.ScreenUpdating = False For r = LastRow To 1 Step -1 If Application.WorksheetFunction.CountA (Rows(r)) = 0 _ Then Rows(r).Delete Else If ActiveSheet.Cells(r, 15).Value = "Total" _ Then Rows(r).Delete Else If ActiveSheet.Cells(r, 15).Value = "Value" _ Then Rows(r).Delete Else If ActiveSheet.Cells(r, 15).Value = "" _ Then Rows(r).Delete Next r Next wks End Sub . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looping through charts in a worksheet | Excel Programming | |||
Looping through ComboBoxes in a worksheet | Excel Programming | |||
Looping checkboxes embedded in worksheet | Excel Programming | |||
Looping down list and each time copying to another worksheet | Excel Programming | |||
building a text string while looping though a worksheet | Excel Programming |