Clear Contents on Multiple Worksheets

[reberryjr]

I'm trying to clear the contents on multiple worksheets, from row 2 through the last row. I'm running into an issue where I'm trying to skip the worksheet if row 2 is already empty. Here's my code. I'm getting a Next Without For error message.

Sub ClearData()
Application.ScreenUpdating = False
Dim ws As Worksheet
For Each ws In Worksheets
    If Not ws.Name = "Summaries" And Not ws.Name = "Consolidated" And Not ws.Name = "Variables" Then
        If ws.FilterMode = True Then
            ws.ShowAllData
        Else
        End If
        If ws.Range("A2").Value <> "" Then
        With ws.Range("A2:P" & ws.Range("A2" & Rows.Count).End(xlUp).Row)
            .ClearContents
        End With
        Else
    End If
Next ws
Application.ScreenUpdating = True
End Sub

 

[vcoolio]

Hello Reberryjr,

Try the following version of your code:-

Sub ClearData()

Dim ws As Worksheet

Application.ScreenUpdating = False

For Each ws In Worksheets
             If ws.Name <> "Summaries" And ws.Name <> "Consolidated" And ws.Name <> "Variables" Then
             If ws.Range("A2") <> "" Then
             ws.UsedRange.Offset(1).ClearContents
             End If
       End If
Next ws

Application.ScreenUpdating = True

End Sub

Test it in a copy of your workbook first.

I hope that this helps.

Cheerio,
vcoolio.

 

[reberryjr]

Worked like a charm! Thank you!

 

[vcoolio]

You're welcome. I'm glad that I was able to help.

Cheerio,
vcoolio.