Using VBA to find 0s and clear adjacent cells

[rachel06]

Hello,

I'm using the below code to delete entire rows of data that have a value of 0. Is there any way to alter this code to clear the contents of columns A-R if column B=0? Know that the headers on this data set start in row 9.

Sheets("Sheet1").Select
Worksheets("Sheet1").Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Name = "Summary"

With Range("Summary")
.AutoFilter Field:=1, Criteria1:="0"
.Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
Worksheets("Summary").AutoFilterMode = False
End With

I'm sure this is something that's super easy, but I'm just not having luck. I'm not able to delete the rows in this instance because there are pivot tables to the right of the data.

TIA!

 

[DanteAmor]

Try this

Sub test()
  Sheets("Sheet1").Select
  With Range("A9:R" & Range("B" & Rows.Count).End(xlUp).Row)
    .AutoFilter Field:=2, Criteria1:="0"
    .Offset(1).ClearContents
    ActiveSheet.AutoFilterMode = False
  End With
End Sub

 

[rachel06]

Brilliant! A9:R is the part I was getting wrong. Makes so much sense now.

Thanks so much!!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.