Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
For each worksheet in workbook problem
Brilliant, Charles.
Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem w/code to copy worksheet to new workbook | Excel Programming | |||
problem with formatting text in every worksheet same workbook | Excel Programming | |||
Copy sheet from one workbook to another workbook problem | Excel Programming | |||
Workbook crashes after renaming a worksheet - References problem? | Excel Programming |