Solved

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

Posted on 2014-11-28
6
142 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
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.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

808 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