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.


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.593 / Virus Database: 376 - Release Date: 20/02/2004

..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


In article ,
"Stuart" wrote:

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.


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.593 / Virus Database: 376 - Release Date: 20/02/2004

Yes, I can see that now.
Many thanks.

Regards.

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


In article ,
"Stuart" wrote:

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.


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.593 / Virus Database: 376 - Release Date: 20/02/2004




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.593 / Virus Database: 376 - Release Date: 20/02/2004