truncate text in each cell in column

[Jake]

Hello,
I would like to loop through text data in a column and truncate the cells to
35 characters. I don't know how many cells will contain data and there are
empty cells. I can have 50,000 or more cells in the column. I thought this
code would work:
For Each rngCell In Columns("N:N")
rngCell.Value = Left(CStr(rngCell), 35)
Next rngCell
but it gives a type mismatch error.
Also this doesn't loop through each cell in the column as I thought it would.
Thanks for any help.
Jake

 

[Chip Pearson]

The problem is that rngCell is being set to then entire column N. Try

For Each rngCell In Columns("N:N").Cells

or, better,

For Each rngCell In Application.Intersect( _
ActiveSheet.UsedRange, ActiveSheet.Columns("N")).Cells

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[JE McGimpsey]

One way:

Dim rngCell As Range

For Each rngCell In Range("N1:N" & _
Range("N" & Rows.Count).End(xlUp).Row)
With rngCell
If Not IsEmpty(.Value) Then _
.Value = Left(.Text, 35)
End With
Next rngCell

 

[Jake]

Works fine. Thanks!

The problem is that rngCell is being set to then entire column N. Try

For Each rngCell In Columns("N:N").Cells

or, better,

For Each rngCell In Application.Intersect( _
ActiveSheet.UsedRange, ActiveSheet.Columns("N")).Cells

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)