For each worksheet in workbook problem


E

elf27

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
 
Ad

Advertisements

Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top