Macro clear contents & loop

[Abvlecxe]

Hello I need some help with the following macro which I am using to clear the contents of specific cells.
Basically the macro should select cells C9:F9 clear the contents and then loop down 7 rows to C16:F16, clear those cells and then keep on looping by 7 rows until it reaches the lastrow but I can’t seem to get it to work so any help would be appreciated, thanks in advance


Sub ClearDataInput()
Dim lastRow As Long
Dim Row As Long
Dim x As Long
Application.ScreenUpdating = False
Row = 9
For x = 9 To lastRow
With Sheet5
.Select
lastRow = .Range("c7").SpecialCells(xlCellTypeLastCell).Row
.Range("C" & Row & ": f" & Row).Select
Selection.ClearContents
Row = Row + 7

End With
Next x

Application.ScreenUpdating = True
End Sub

 

[mikerickson]

How about

Dim lastRow as Long, cCells as Range
With Sheet5
     lastRow = .Range("c7").SpecialCells(xlCellTypeLastCell).Row

    Set cCells = .Range("C9:F9")

    For i = 9 to lastRow Step 7
        Set cCells = Application.Union(cCells, .Rows(i).Range("C1:F1")
    Next i
End With

cCells.ClearContents

 

[Abvlecxe]

Thanks but that doesn't work. Have you set defined all the variables in your code?

It doesn't like:
For i = 9 To lastRow Step 7
Set cCells = Application.Union(cCells, .Rows(i).Range("C1:F1")

How about

Dim lastRow as Long, cCells as Range
With Sheet5
     lastRow = .Range("c7").SpecialCells(xlCellTypeLastCell).Row

    Set cCells = .Range("C9:F9")

    For i = 9 to lastRow Step 7
        Set cCells = Application.Union(cCells, .Rows(i).Range("C1:F1")
    Next i
End With

cCells.ClearContents

 

[mikerickson]

Yeah, I missed the right parenthesis. Oops.