Solved

Cut/remove characters from a Cell after 20 VBA

Posted on 2014-11-02
7
87 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
  • 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now