Trying to use VBA to clear cell contents but not the formula/format from specified cell

[Rashaud35]

I am using the following code to clear the contents of a row, as part of a "clear button" I've created in my spreadsheet, but there's one Cell in each row that has a quotient formula in it. I would like to clear the Contents of the row, but keep the format (font type, center alignment, etc.) and the formula in the specified cell.

Here's the VBA I'm attempting to use:

Range("A9:J9").SpecialCells(xlBlanks).EntireRow.ClearContents
Range("A12:J12").SpecialCells(xlBlanks).EntireRow.ClearContents
Range("A15:J15").SpecialCells(xlBlanks).EntireRow.ClearContents
Range("A18:J18").SpecialCells(xlBlanks).EntireRow.ClearContents
Range("A21:J21").SpecialCells(xlBlanks).EntireRow.ClearContents
Range("A24:J24").SpecialCells(xlBlanks).EntireRow.ClearContents

The Quotient formula will reside in cells G9, G12, G15, G18, G21 and G24

G9=QUOTIENT(F9,D9)
G12=QUOTIENT(F12,D12)
G15=QUOTIENT(F15,D15)
G18=QUOTIENT(F18,D18)
G21=QUOTIENT(F21,D21)
G24=QUOTIENT(F24,D24)

Any help you all can provide would be astounding! Thanks!

[#2 · Accepted solution]

@Rashaud35 

Range("A9:J9").SpecialCells(xlBlanks).EntireRow is the same as Range("A9").EntireRow and as Rows(9).

So there is no need to specify A9:J9 and SpecialCells(xlBlanks)

You might use

Rows(9).SpecialCells(xlCellTypeConstants).ClearContents

[#3]

@Hans Vogelaar Hey Hans, thank you so much for your help with this and sorry about the late response. I think this worked, however, I still get a runtime error. I changed all the "range" to "row" and it cleared the cells and kept the formula in place, but the formatting still gets cleared out (font type, currency, etc.). I've attached a screenshot of the the error.

[#4]

@Rashaud35 

You'll get that error of the specified row doesn't contain any fixed values. To avoid the error, insert the line

    On Error Resume Next

at the beginning of the macro.

But ClearContents should only delete the cell contents, not their formatting. When you enter a value again, you should see the previously set formatting.

[#5]

@Hans Vogelaar Yep, you're right buddy! Thanks so much for your help with this! You're awesome!