Remove data that doesn't match ??##?##

I have data in column A that is in the format of ??##?## i.e. it might be
CD01A01 for instance.

Any column containing this data type I want to keep (including the data in
the row)

I need a code that will keep this sort of data and delete all other rows that
don't match this format

Hope this makes sense


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200510/1

Hi Crowbar,

Crowbar via OfficeKB.com wrote:
> I have data in column A that is in the format of ??##?## i.e. it
> might be CD01A01 for instance.
>
> Any column containing this data type I want to keep (including the
> data in the row)
>
> I need a code that will keep this sort of data and delete all other
> rows that don't match this format

Here's one way:

Sub test()
Dim rngTarget As Range
Dim lRow As Long
Dim lLastRow As Long

With Worksheets("Sheet1")
lLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
For lRow = 1 To lLastRow
If Not .Cells(lRow, 1).Value Like "??##?##" Then
If Not rngTarget Is Nothing Then
Set rngTarget = Application.Union(rngTarget, _
.Cells(lRow, 1).EntireRow)
Else
Set rngTarget = .Cells(lRow, 1).EntireRow
End If
End If
Next lRow
End With

If Not rngTarget Is Nothing Then
rngTarget.Delete shift:=xlUp
Set rngTarget = Nothing
End If
End Sub

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

On Wed, 26 Oct 2005 20:59:00 GMT, "Crowbar via OfficeKB.com" <u15117@uwe>
wrote:

>I have data in column A that is in the format of ??##?## i.e. it might be
>CD01A01 for instance.
>
>Any column containing this data type I want to keep (including the data in
>the row)
>
>I need a code that will keep this sort of data and delete all other rows that
>don't match this format
>
>Hope this makes sense

In Tools/References, select "Microsoft VBScript Regular Expressions 5.5"

Then I believe this code will do what you require. Note that you may need to
change the FirstRow constant as I don't know where you want to start checking.
The LastRow constant is set based on the last entry in Column A.


=============================
Sub RemRw()
Dim regex As Object
Dim i As Long
Dim Temp As String
Dim LastRow As Long

Const FirstRow As Long = 1 'May need to be changed
Const Ptrn As String = "^\w\w\d\d\w\d\d$"

Set regex = CreateObject("vbscript.regexp")
regex.Pattern = Ptrn

LastRow = [A65536].End(xlUp).Row

For i = LastRow To FirstRow Step -1
Temp = Cells(i, 1).Text

If regex.Test(Temp) = False Then
Cells(i, 1).EntireRow.Delete
End If
Next i

End Sub
==========================


--ron

On Wed, 26 Oct 2005 20:59:00 GMT, "Crowbar via OfficeKB.com" <u15117@uwe>
wrote:

>I have data in column A that is in the format of ??##?## i.e. it might be
>CD01A01 for instance.
>
>Any column containing this data type I want to keep (including the data in
>the row)
>
>I need a code that will keep this sort of data and delete all other rows that
>don't match this format
>
>Hope this makes sense


Jake's post reminded me of the "Like" operator, which allows me to simplify my
routine considerably. (I've had regular expressions on the brain lately ;-)))

===================
Sub RemRw()
Dim i As Long
Dim Temp As String
Dim LastRow As Long

Const FirstRow As Long = 1 'May need to be changed
Const Ptrn As String = "??##?##"

LastRow = [A65536].End(xlUp).Row

For i = LastRow To FirstRow Step -1
Temp = Cells(i, 1).Text

If Not Temp Like Ptrn Then
Cells(i, 1).EntireRow.Delete
End If
Next i

End Sub
======================
--ron