Solved

Cut/remove characters from a Cell after 20 VBA

Posted on 2014-11-02
7
94 Views
Last Modified: 2014-11-04
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
0
Comment
Question by:swjtx99
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 45

Expert Comment

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

Open in new window

0
 

Author Comment

by:swjtx99
ID: 40418744
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
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40418763
please post your workbook
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 18

Accepted Solution

by:
krishnakrkc earned 500 total points
ID: 40418774
    Dim r   As Range
    
    With Sheets("Sheet1")
        Set r = Intersect(.UsedRange, .Columns("h:h")) 'adjust Col H to suit
    
        For Each cell In r 'Edit to desired range
            cell.Value = Left(cell, 25)
        Next cell
    End With

Open in new window

0
 

Author Closing Comment

by:swjtx99
ID: 40420873
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
0
 

Author Comment

by:swjtx99
ID: 40420874
Hi Aikimark,

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

swjtx99
0
 
LVL 18

Expert Comment

by:krishnakrkc
ID: 40421399
That's because you were using the currentregion property of the cell.
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question