Solved

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

Posted on 2014-11-28
6
134 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 45

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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 45

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 45

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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 …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

706 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

18 Experts available now in Live!

Get 1:1 Help Now