Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How can I edit this script to remove some data from colum A1?

Posted on 2014-11-28
6
Medium Priority
?
150 Views
Last Modified: 2014-11-28
Hi.  I would like to modify the following script to be used in an excel Macro.

Sub Clean_Up()
    Dim rng, rng2 As Range
    Dim cl As Object
    
    Set rng = Range("A1:A" & Cells.SpecialCells(xlLastCell).Row)
    
    For Each cl In rng
        If cl.Value <> "" And (cl.Offset(0, 18).Value = 0) Then
            If rng2 Is Nothing Then
                Set rng2 = cl
            Else
                Set rng2 = Union(rng2, cl)
            End If
        End If
    Next cl
    rng2.EntireRow.Delete
    
    Columns("T:Z").EntireColumn.Delete
    Columns("B:R").EntireColumn.Delete
    
End Sub

Open in new window


At the end of the script, it should look in column A1 and remove any alpha numeric characters after the first string of numbers.

For instance, in this scenario, in column A1:

ABC123 DESCRIPTION OF PRODUCT
BCD1234567UA ANOTHER PRODUCT DESCRIPTION..


Where data is remaining in Column A after the script has run, it needs to remove the alpha numeric characters after the initial part number.   In other words where it finds the first space after the first set of data, it should remove the rest.
Something like this:

ABC123
BCD1234567UA

Thanks in advance for your assistance.
0
Comment
Question by:100questions
  • 3
  • 3
6 Comments
 
LVL 49

Accepted Solution

by:
Martin Liss earned 2000 total points
ID: 40470792
Add these lines at the end of the macro.

    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

Open in new window

And BTW when you do this

Dim rng, rng2 As Range

rng is left defined as a Variant, so this is better
Dim rng As Range
Dim rng2 As Range
0
 

Author Comment

by:100questions
ID: 40470825
Ok, thank you.  I will try this shortly.  Lastly, if I want to modify the script so that if there are numbers in column A, but if column S is 0, not to delete the line, should I open a new ticket?
0
 

Author Comment

by:100questions
ID: 40470903
Martin, thanks very much.   I tried however for some reason I get a Compile Error:  Variable not defined.
And it's pointing to lngRow...
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 49

Assisted Solution

by:Martin Liss
Martin Liss earned 2000 total points
ID: 40470913
I'm sorry, just add Dim lngRow As Long at the top.
0
 

Author Closing Comment

by:100questions
ID: 40470927
Perfect, works excellent.  Thanks.
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 40470940
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

783 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