Clear Contents of Specific Ranges

user_16087155 0
Viewed 31

I am still new to VBA. I wanted to clear all the contents of the data (Row 3 to Row 12, Row 15 to Row 24, etc) below the yellow headers, without deleting all of the headers as shown in the photos (Fig 1 becomes Fig. 2). The headers go all the way down to row 109 (increments of 12 from Row 1, so Rows 1,13,25 ...85). I have a code but its too basic and long:

 Sub Clear_All()

 Set Unitsheet = ThisWorkbook.Worksheets("Sheet"1)
  Unitsheet.Range("A3:F12").ClearContents
 Unitsheet.Range("A15:F24").ClearContents
 .
 .
 .
 .'up to
   Unitsheet.Range("A111:F120").ClearContents

End Sub

I need a code that is short, since the rows may reach up to more than 1000. Any help will be much appreciated.

Fig. 1

|
|
V

Fig. 2

rows range
1 Answers
Sub clear()
Dim i, rows As Long
rows = ActiveSheet.UsedRange.rows.Count
For i = 1 To rows
    If Sheet1.Cells(i, 1).Interior.ColorIndex = -4142 Then
        Sheet1.Cells(i, 1).EntireRow.ClearContents
    End If
Next
End Sub

this function finds all used rows in sheet1
it iterates all rows , if color of cell in A column has no color index (-4142) it clears all contents in entire row

user_128607570
Related