For each worksheet in workbook problem
I'm having trouble with a for each statement.
The code below is intended to clear all data (starting with rows with numbers) on each worksheet except sheet1. If I run the macro while on sheet1, it works like a charm. If I run the macro while on any other sheet, it only clears the data from that sheet. I can't figure out why. --------------------------- Sub ClearAll() Dim w As Worksheet Dim wb As Workbook Dim T As Long Dim FirstRow As Long Dim LastRow As Long Set wb = ActiveWorkbook Application.ScreenUpdating = False For Each w In wb.Worksheets If Not w.Name = Sheet1.Name Then FirstRow = 0 LastRow = Cells(w.Rows.Count, "A").End(xlUp).Row With w T = 1 Do Until FirstRow < 0 If IsNumeric(w.Cells(T, "A")) Then If w.Cells(T, "A").Value 0 Then FirstRow = T ElseIf T = LastRow Then FirstRow = LastRow ElseIf T 50 Then FirstRow = 1 End If Else: FirstRow = 0 End If T = T + 1 Loop w.Range("A" & FirstRow & ":A" & LastRow).EntireRow.Delete End With End If Next Application.ScreenUpdating = True End Sub |
For each worksheet in workbook problem
LastRow = w.Cells(w.Rows.Count, "A").End(xlUp).Row
"elf27" wrote in message ... I'm having trouble with a for each statement. The code below is intended to clear all data (starting with rows with numbers) on each worksheet except sheet1. If I run the macro while on sheet1, it works like a charm. If I run the macro while on any other sheet, it only clears the data from that sheet. I can't figure out why. --------------------------- Sub ClearAll() Dim w As Worksheet Dim wb As Workbook Dim T As Long Dim FirstRow As Long Dim LastRow As Long Set wb = ActiveWorkbook Application.ScreenUpdating = False For Each w In wb.Worksheets If Not w.Name = Sheet1.Name Then FirstRow = 0 LastRow = Cells(w.Rows.Count, "A").End(xlUp).Row With w T = 1 Do Until FirstRow < 0 If IsNumeric(w.Cells(T, "A")) Then If w.Cells(T, "A").Value 0 Then FirstRow = T ElseIf T = LastRow Then FirstRow = LastRow ElseIf T 50 Then FirstRow = 1 End If Else: FirstRow = 0 End If T = T + 1 Loop w.Range("A" & FirstRow & ":A" & LastRow).EntireRow.Delete End With End If Next Application.ScreenUpdating = True End Sub |
For each worksheet in workbook problem
Brilliant, Charles.
Thanks! |
All times are GMT +1. The time now is 02:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com