Delete rows that don't contain...
 

Hi,

I'm trying to write a macro the deletes all rows that don't contain the
numbers 10 in row A. The numbers will range from 102000 to 1099999. I want to
delete all rows that don't have any of these numbers in the first column.

Thanks for any help

Delete rows that don't contain...
 

Hi,

I think your saying that if the number in column A doesn't begin with 10
then delete the entire row. If that's correct then right click your sheet
tab, view code and paste this in and run it. If that's wrong then post back.

Sub Standard()
mycolumn = "A" 'Change to suit
Dim MyRange, MyRange1 As Range
LastRow = Cells(Rows.Count, mycolumn).End(xlUp).Row
Set MyRange = Range(mycolumn & "1:" & mycolumn & LastRow)
For Each c In MyRange
If InStr(CStr((c.Value)), "10") < 1 Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next
If Not MyRange1 Is Nothing Then
MyRange1.Delete
End If
End Sub

Mike

"nrfinsa" wrote:

Hi,

I'm trying to write a macro the deletes all rows that don't contain the
numbers 10 in row A. The numbers will range from 102000 to 1099999. I want to
delete all rows that don't have any of these numbers in the first column.

Thanks for any help

Delete rows that don't contain...
 

Hi,

Reading your post again you do say 'Don't contain' so change

If InStr(CStr((c.Value)), "10") < 1 Then

to

If InStr(CStr(c.Value), "10") = 0 Then

and it will dlete every row that doesn't 'contain' a 10. Note i've knocked
out a set of parenthesis that weren't necessary.

Mike

"Mike H" wrote:

Hi,

I think your saying that if the number in column A doesn't begin with 10
then delete the entire row. If that's correct then right click your sheet
tab, view code and paste this in and run it. If that's wrong then post back.

Sub Standard()
mycolumn = "A" 'Change to suit
Dim MyRange, MyRange1 As Range
LastRow = Cells(Rows.Count, mycolumn).End(xlUp).Row
Set MyRange = Range(mycolumn & "1:" & mycolumn & LastRow)
For Each c In MyRange
If InStr(CStr((c.Value)), "10") < 1 Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next
If Not MyRange1 Is Nothing Then
MyRange1.Delete
End If
End Sub

Mike

"nrfinsa" wrote:

Hi,

I'm trying to write a macro the deletes all rows that don't contain the
numbers 10 in row A. The numbers will range from 102000 to 1099999. I want to
delete all rows that don't have any of these numbers in the first column.

Thanks for any help

Delete rows that don't contain...
 

Thanks Mike,

That worked perfectly :-)

Neville

Delete rows that don't contain...
 

Mike

Is it possible to add other choices to keep rows, This works fine for my
number range, but I would like to keep 17 and 2 types of text SHS and TFG and
then delete all other rows

Regards

Johnny

"Mike H" wrote:

Hi,

I think your saying that if the number in column A doesn't begin with 10
then delete the entire row. If that's correct then right click your sheet
tab, view code and paste this in and run it. If that's wrong then post back.

Sub Standard()
mycolumn = "A" 'Change to suit
Dim MyRange, MyRange1 As Range
LastRow = Cells(Rows.Count, mycolumn).End(xlUp).Row
Set MyRange = Range(mycolumn & "1:" & mycolumn & LastRow)
For Each c In MyRange
If InStr(CStr((c.Value)), "10") < 1 Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next
If Not MyRange1 Is Nothing Then
MyRange1.Delete
End If
End Sub

Mike

"nrfinsa" wrote:

Hi,

I'm trying to write a macro the deletes all rows that don't contain the
numbers 10 in row A. The numbers will range from 102000 to 1099999. I want to
delete all rows that don't have any of these numbers in the first column.

Thanks for any help