Invalid use of Null

  • Thread starter Thread starter Stuart
  • Start date Start date
S

Stuart

For Each C In .UsedRange
With C
.Value = (WorksheetFunction.Clean _
(WorksheetFunction.Trim(.Text)))
.WrapText = False
End With
Next

appears to work fine.

However, in amending the code so as to operate on
cols A:D only, using

For Each C In .UsedRange.Columns("A:D")
With C
.Value = (WorksheetFunction.Clean _
(WorksheetFunction.Trim(.Text)))
.WrapText = False
End With
Next

I get "Invalid use of Null" on the .Value line.

Why is this, and how do I resolve it please?

Regards.
 
..Columns(1) returns a Column, not a Range. Try:

Dim C As Range
With ActiveSheet
For Each C In .UsedRange.Columns("A:D").Cells
With C
.Value = (WorksheetFunction.Clean _
(WorksheetFunction.Trim(.Text)))
.WrapText = False
End With
Next C
End With
 
Yes, I can see that now.
Many thanks.

Regards.

JE McGimpsey said:
.Columns(1) returns a Column, not a Range. Try:

Dim C As Range
With ActiveSheet
For Each C In .UsedRange.Columns("A:D").Cells
With C
.Value = (WorksheetFunction.Clean _
(WorksheetFunction.Trim(.Text)))
.WrapText = False
End With
Next C
End With
Click to expand...
 
Back
Top