Solved

Excel VBA truncate cells in a column

Posted on 2014-04-07
2
1,286 Views
Last Modified: 2014-04-09
Hi,

I need to truncate all the data in each cell in column I to just the first 20 characters (alpha & numeric) I was trying to use this but this example truncates the data in all columns. It doesn't look like it should, but it does.

Edit: I'm not trying to delete spaces, just make the max = the first 20 characters.

    Sheets("Sheet1").Select
    Columns("I:I").Select
    Dim rCell As Range
    For Each rCell In Range("I1", Cells(Rows.Count, 1).End(xlUp))
        rCell = Left(rCell, 20)
    Next rCell
0
Comment
Question by:swjtx99
2 Comments
 
LVL 35

Accepted Solution

by:
Kimputer earned 500 total points
ID: 39984757
Replace one line:

For Each rCell In ActiveSheet.UsedRange.Columns("I").Cells
0
 

Author Closing Comment

by:swjtx99
ID: 39988970
Works!

Thanks,

swjtx99
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS Excel: simple question #2 10 40
Excel Power Pivot - Aggregation 7 81
Excel Formula return each instance from list 2 75
EXCEL: Random Select and Copy 10% of the Row 7 143
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
Viewers will learn how to apply various conditional formatting in Excel 2013.
Viewers will learn how to share Excel data with others from desktop Excel, as well as Excel Online via OneDrive, and embed an Excel file on a website.

820 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