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