Solved

How can I modify this script to be used in an Excel macro?

Posted on 2014-12-01
2
70 Views
Last Modified: 2014-12-01
Script:
Option Explicit
Sub Clean_Up()
    Dim rng As Range
    Dim rng2 As Range
    Dim cl As Object
    Dim lngRow As Long
        
    Set rng = Range("A1:A" & Cells.SpecialCells(xlLastCell).Row + 1)
    
    On Error Resume Next
    For lngRow = 1 To rng.Rows.Count
    ActiveSheet.Cells(lngRow, 1).Value = Split(ActiveSheet.Cells(lngRow, 1).Value, " ")(0)
    Next
    On Error GoTo 0
    
    Columns("T:Z").EntireColumn.Delete
    Columns("B:R").EntireColumn.Delete
    
    
End Sub

Open in new window


I would like to modify the script so that when it find numbers in column A, that it does not remove the leading 0's to those numbers but leaves them intact, and still goes on with the function set out for the macro.

For instance if it finds a line in column A which has the following:
00001  BULK DESCRIPTION 20 lbs

It will simply leave it as 1.

I want it to be:
00001

It seems to work fine if there are alpha characters, but if it's numbers, then it removes leading zeros and it should not.

Can we modify the script if possible?

Any assistance is appreciated.

Thank you.
0
Comment
Question by:100questions
2 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40473978
Insert an extra line before row 12:

 ActiveSheet.Cells(lngRow, 1).NumberFormat = "@"

Open in new window


That converts that cell to text, and so your 00001 remains 00001.
0
 
LVL 48

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 40473997
Hi,

or in one line

ActiveSheet.Cells(lngRow, 1).Formula = "'" & Split(ActiveSheet.Cells(lngRow, 1).Value, " ")(0)

Regards
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

746 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

10 Experts available now in Live!

Get 1:1 Help Now