[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 152
  • Last Modified:

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

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
100questions
Asked:
100questions
  • 3
  • 3
2 Solutions
 
Martin LissOlder than dirtCommented:
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
 
100questionsAuthor Commented:
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
 
100questionsAuthor Commented:
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: 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.

 
Martin LissOlder than dirtCommented:
I'm sorry, just add Dim lngRow As Long at the top.
0
 
100questionsAuthor Commented:
Perfect, works excellent.  Thanks.
0
 
Martin LissOlder than dirtCommented:
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now