Range.ClearContents not performing as expected - it removes the formatting along with the contents

Question

How can I troubleshoot the cause behind this failed bit of code? I've searched all over the web including Microsoft and cannot see anything wrong with how I wrote this script. But, contrary to all I read, the formatting of the cleared cell also disappears. I am stumped and would appreciate any ideas. Maybe it is a known bug? maybe there is a workaround? I tried to set a cell formatting but that didn't work either.

the code DOES empty the cell contents just fine, but i really really want to retain the formatting, as the default is very tiny font size and disruptive to the viewer experience.

here's the code I'm using. i cross posted over at excelguru but so far only views, no ideas.

Option Explicit
Private Sub CommandButton1_Click()
Dim SKU As String, Qty As String, TFC As String, LTFC As String
Worksheets("dashboard - beta").Select
SKU = Range("D6")
Qty = Range("L2")
TFC = Range("L3")
LTFC = Range("M3")
Worksheets("Costing Log").Select
Worksheets("Costing Log").Range("B4").Select
If Worksheets("Costing Log").Range("B4").Offset(1, 0) <> "" Then
Worksheets("Costing Log").Range("B4").End(xlDown).Select
End If
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = SKU
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Qty
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = TFC
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = LTFC


Worksheets("dashboard - beta").Select
Worksheets("dashboard - beta").Range("a5:a34").ClearContents
Worksheets("dashboard - beta").Range("L2").ClearContents

Worksheets("dashboard - beta").Range("j10:j34").ClearContents

End Sub

Sub FontSize()
'code for changing font size
Worksheets("dashboard - beta").Range("L2").Font.Size = 22
End Sub

`ClearContents` keeps formatting. Do you have other code that might clear all - or is condtional formatting applied. BTW: you definitly should read [How to avoid using select](https://stackoverflow.com/q/10714251/16578424) — Ike, Jul 20 '22 at 20:30
Im guessing that the formatting is being applied by Conditional Formatting rules. By removing the cell value, you're likely changing which formatting rules are applied, which is changing the format of the cell. — Toddleson, Jul 20 '22 at 20:48
Ok Ike, you nailed it! There was another module with a different clearContents code. I removed that and now my formatting is fixed as desired. — amyfb, Jul 20 '22 at 20:59
Also, you can edit the "Default" formatting of the workbook. There are the formatting "Styles" in one of the ribbon tabs. Find the Style named "Normal" and edit it. You can choose a larger font size, which will immediately update all cells using that style. When cells have their formatting cleared, they default back to "Normal" — Toddleson, Jul 20 '22 at 21:00
BUT - now I have a new challenge: how can I set up two buttons - one that just clears the cells and keeps the formatting, and , another that moves the data to another tab before clearing the cells. the workflow wants this functionality , and clearly the way I had it isn't cutting the mustard. thanks for additional ideas. ! oh, two buttons, one for logging and one for clearing. duh facepalm me! — amyfb, Jul 20 '22 at 21:01

Tim Williams · Answer 1 · 2022-07-21T17:09:14.010

Seems like you got your answer already, but FYI here's the process without any select/activate:

Private Sub CommandButton1_Click()
    
    Dim wsDB As Worksheet, wsLog As Worksheet
    'use worksheet variables to cut down on repetition
    Set wsDB = ThisWorkbook.Worksheets("dashboard - beta")
    Set wsLog = ThisWorkbook.Worksheets("Costing Log")
    'xlUp is typically more reliable then xlDown,
    '  since it's not affected by gaps in your data
    wsLog.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Resize(1, 4).Value = _
            Array(wsDB.Range("D6").Value, wsDB.Range("L2").Value, _
                  wsDB.Range("L3").Value, wsDB.Range("M3").Value)
    'you can combine ranges in a single string passed to Range()
    wsDB.Range("A5:A34,L2,J10:J34").ClearContents
    
End Sub

Range.ClearContents not performing as expected - it removes the formatting along with the contents

1 Answers1