Solved

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

Posted on 2014-11-28
6
141 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 46

Accepted Solution

by:
Martin Liss earned 500 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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
LVL 46

Assisted Solution

by:Martin Liss
Martin Liss earned 500 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 46

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

776 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