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

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

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

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