Link to home
Start Free TrialLog in
Avatar of swjtx99
swjtx99

asked on

Cut/remove characters from a Cell after 20 VBA

The formula below was working ok but after some other changes, ....it's doing weird stuff to column A (adding "t" to the beginning and "xt" to the end of whatever is in column A whenever it finds more than 25 characters in a cell in Column H.

'    Sheets("Sheet1").Select
'    Columns("H:H").Select
'    For Each cell In Range("H:H").CurrentRegion  'Edit to desired range
'    cell.Value = Left(cell, 25)
'    Next cell

Is there a better way?

Thanks,

swjtx99
Avatar of aikimark
aikimark
Flag of United States of America image

You should trim the value property of the cells:
cell.Value = Left(cell.Value, 25)

Open in new window

Avatar of swjtx99
swjtx99

ASKER

Hi Aikmark,

Thanks. For some reason it's still changing the cell formats for every other cell on the row. I thought it was just column A but it's messing with every other cell too.

Regards,

swjtx99
please post your workbook
ASKER CERTIFIED SOLUTION
Avatar of krishnakrkc
krishnakrkc
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of swjtx99

ASKER

Thanks Krishnakrkc,

Works great although I still don't know why my original code messes with the formats of all cells on the row. Any idea?

Thanks,

swjtx99
Avatar of swjtx99

ASKER

Hi Aikimark,

Sorry I was unable to post the workbook. It has info I can't post. Thanks for trying.

swjtx99
That's because you were using the currentregion property of the cell.