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.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.778 / Virus Database: 525 - Release Date: 15/10/2004

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.

---
Regards,
Norman



"Stuart" wrote in message
...
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.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.778 / Virus Database: 525 - Release Date: 15/10/2004

Regards and thanks.

"Norman Jones" wrote in message
...
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.

---
Regards,
Norman



"Stuart" wrote in message
...
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.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.778 / Virus Database: 525 - Release Date: 15/10/2004






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.778 / Virus Database: 525 - Release Date: 15/10/2004