View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
elf27 elf27 is offline
external usenet poster
 
Posts: 18
Default 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