Quick question on setting a Match String...

[GS7CLW]

Using this code to dalete rows with 7VXX in column A:

    AC = Split(ActiveCell.EntireColumn.Address(, False), ":")
    ActiveColumn = AC(0)
    SearchColumn = "A:A"
    On Error Resume Next
    Set MyRange = Columns(SearchColumn)
    On Error GoTo 0
     If MyRange Is Nothing Then Exit Sub
    MatchString = "7V  "
    Set c = MyRange.Find(What:=MatchString, After:=MyRange.Cells(1), LookIn:=xlValues, LookAt:=xlPart)
    If Not c Is Nothing Then
        Set DelRange = c
        FirstAddress = c.Address
        Do
            Set c = MyRange.FindNext(c)
            Set DelRange = Union(DelRange, c)
        Loop While FirstAddress <> c.Address
    End If
    If Not DelRange Is Nothing Then DelRange.EntireRow.Delete

my problem is with the:
MatchString = "7V "
line.

In column a are data such as:
5D7V
and
7VJH

I want to delete only the rows with 7V as the first 2 characters.
"7V" deletes both &
"7V " does not work at all.

ANY help greatly appreciated.

Hope everyone had a great week end!
cliff

 

[Andrew Poulsom]

Try with "7V*" and LookAt:=xlWhole.

 

[GS7CLW]

You're FAST!
You're GOOD!
and you are absolutely RIGHT!!!!

thanks and have a great day

cliff

 

[Rick Rothstein]

I believe you can replace all of your code after your

SearchColumn = "A:A"

line with the following and achieve the same end result (and I believe it will execute quicker as well)...

On Error Resume Next
Columns("A").Replace "7V*", "#N/A", xlWhole
Columns("A").SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete

My code above will delete every row where the cell in Column A has text constants (that is, not resulting from a formula) beginning with "7V" provided Column A has no displayed errors.

 

[GS7CLW]

Works perfectly -- AND -- we all know the less code the faster it runs.
Thanks for the input. Works GREAT!

cliff

 

[Rick Rothstein]

AND -- we all know the less code the faster it runs

That is not always true. For an example, click the following link

http://www.xbeat.net/vbspeed/c_GetFile.htm

and scroll through the five code modules posted there and notice the length of each one (the first four are completely displayed in their window whereas the last one requires you to scroll quite a bit in order to see all of it)... you might find it surprising that the last code module is more than twice as fast as the any of the preceding ones.