CODEWITHSUNDEEP
excelvba
user9567082
user9567082

Reputation: 11

Conditional Formatting - Skip empty cells/values

I'm new to macros. I created a very simple macro which allows a range of cells to be formatted within a worksheet. The macro works fine within the worksheet however when I save the document as a csv file there are several trailing commas. I know that the trailing commas are a result of formatting that occurred with the empty values of the worksheet. I need the macro to only format the cells that contain data, but I also need to keep the range of cells. Is this possible? I've tried conditional formatting, CountA formulas, IsEmpty macros,and Is/Then/Else macros but nothing seems to work. I included an example of the data that I have been working with, any help will be appreciated. 

Sub FormatDollarAmount()

    Set MyCellRange = Range("F2:F51")

    If (Not (IsEmpty("F2:F51"))) Then

        Range("F2:F51").NumberFormat = "#,##0.00;_(@_)"

    Else

    End If

End Sub

Upvotes: 1

Views: 55

Answers (2)

cybernetic.nomad
cybernetic.nomad

Reputation: 6368

You can loop through the cells:

Sub FormatDollarAmount()
    Set MyCellRange = Range("F2:F51")
    For each myCell in myCellRange
        If (Not (IsEmpty(myCell))) Then
            myCell.NumberFormat = "#,##0.00;_(@_)"
        Else

        End If
    Next myCell
End Sub

Upvotes: 0

user4039065
user4039065

Reputation:

As long as this is titled regarding conditional formatting, here is a conditional formatting response that won't require re-running every time the data changes.

with worksheets("sheet1")
    with .range(.cells(2, "F"), .cells(.rows.count, "F").end(xlup))
        .formatconditions.delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=sign(len($f2))"
        .FormatConditions(.FormatConditions.Count).numberformat = "#,##0.00;_(@_)"
    end with
end with

Upvotes: 0

Related Questions