Invalid use of Null error

[Stuart]

I'm using the following code to Clean and Trim data in Col B.
Most cells in the range contain data. Most data is text.
Cell ("B1") appears to be empty.

Immediately I run the following code, I receive an "Invalid use
of Null" error in "B1" :

Sub InitialFormat()

Dim C As Range, FormatRng As Range

Set FormatRng = ActiveSheet.UsedRange.Columns(2)
For Each C In FormatRng
With C
.RowHeight = 12.75
If Not IsEmpty(C) Then
.Value = (WorksheetFunction.Clean _
(WorksheetFunction.Trim(.Text)))
End If
.Rows.AutoFit
End With
Next
End Sub

Why is this please?

Regards.

 

[Norman Jones]

Hi Stuart,

Try changing:

Set FormatRng = ActiveSheet.UsedRange.Columns(2)

to:

Set FormatRng = ActiveSheet.UsedRange.Columns(2).Cells

As your code stands, C refers to a column rather than cell and C.Value
references an array of values.

 

[Stuart]

Regards and thanks.

Hi Stuart,

Try changing:


to:

Set FormatRng = ActiveSheet.UsedRange.Columns(2).Cells

As your code stands, C refers to a column rather than cell and C.Value
references an array of values.