truncate text in each cell in column
 

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

truncate text in each cell in column
 

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)



On Thu, 27 Nov 2008 07:06:00 -0800, Jake
wrote:

For Each rngCell In Columns("N:N")
rngCell.Value = Left(CStr(rngCell), 35)
Next rngCell

truncate text in each cell in column
 

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



In article ,
Jake wrote:

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

truncate text in each cell in column
 

Works fine. Thanks!

"Chip Pearson" wrote:


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)



On Thu, 27 Nov 2008 07:06:00 -0800, Jake
wrote:

For Each rngCell In Columns("N:N")
rngCell.Value = Left(CStr(rngCell), 35)
Next rngCell