Forum Discussion
Trying to use VBA to clear cell contents but not the formula/format from specified cell
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!
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
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
HansVogelaar 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.
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 Nextat 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.
