Delete Row if cell in column E is '1037'

Eagles!

unread,

Jan 9, 2003, 2:28:33 PM1/9/03

to

Good morning. Was hoping someone could help me with a small problem. I
have a rather large macro that feeds from a single data sheet of roughly
5,000 rows. The problem is this - There are a few rows of data I'd like to
exclude prior to the macro manipulating the data.

To be more specific, each row has a 4 digit product type in column E. There
are 3 product types I'd like excluded: 1034,1035 & 1037.

How can I search the data, and eliminate the rows with these 3 product
types?

Thank you!

Go Eagles!

Ron de Bruin

unread,

Jan 9, 2003, 2:49:43 PM1/9/03

to

Try somthing like this

Sub DeleteRows()
Dim FoundCell As Range
Set FoundCell = Range("E:E").find("1034")
Do Until FoundCell Is Nothing
FoundCell.EntireRow.Delete
Set FoundCell = Range("E:E").FindNext
Loop
End Sub

Or for all numbers

Sub DeleteRows_2()
Columns(5).Replace What:="1034", Replacement:=""
Columns(5).Replace What:="1035", Replacement:=""
Columns(5).Replace What:="1037", Replacement:=""
Columns(5).Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

--
Regards Ron de Bruin
( Win XP SP-1 XL2002 SP-2)
www.rondebruin.nl

"Eagles!" <G...@noreply.com> schreef in bericht
news:3e1dcda6$0$21637$4c41...@reader0.ash.ops.us.uu.net...

Paul

unread,

Jan 9, 2003, 3:01:58 PM1/9/03

to

Eagles, try this
Sub Delete_Rows()
' This macro deletes all rows on the active worksheet
' that have 1034, 1035, 1037 column E.
Dim rng As Range, cell As Range, del As Range
Set rng = Intersect(Range("E:E"), ActiveSheet.UsedRange)
For Each cell In rng
If (cell.Value) = "1034" _
Or (cell.Value) = "1035" _
Or (cell.Value) = "1037" Then
If del Is Nothing Then
Set del = cell
Else: Set del = Union(del, cell)
End If
End If
Next
On Error Resume Next
del.EntireRow.Delete
End Sub

--
Paul, ** remove news from my email address to reply by email **
Always remember to backup your date before trying something new
Using Excel '97

"Eagles!" <G...@noreply.com> wrote in message
news:3e1dcda6$0$21637$4c41...@reader0.ash.ops.us.uu.net...

Eagles!

unread,

Jan 9, 2003, 3:17:20 PM1/9/03

to

Sweet Ron! Thanks so much!

"Ron de Bruin" <ronde...@kabelfoon.nl> wrote in message
news:eJ965gBuCHA.2308@TK2MSFTNGP09...

Sbell

unread,

Jan 9, 2003, 3:34:22 PM1/9/03

to

Paul,

When I have tried a macro like this it seems to take forever to get through
all the rows.
I even turn ScreenUpdating off.

Can you make any suggestions to speed this up (Excel97 SR2, Windows NT)

steve

"Paul" <newspb...@uga.edu> wrote in message
news:#ebfUnBuCHA.2492@TK2MSFTNGP11...

Paul

unread,

Jan 9, 2003, 7:01:08 PM1/9/03

to

Sbell, ran it on 65,000 rows in less than 5 sec.
--
Paul
Always backup your data before trying something new
Using Excel '97

** remove news from my email address to reply by email **

Please post any response to the newsgroups so others can benefit from it

"Sbell" <sb...@sunclipse.com> wrote in message
news:erZi15BuCHA.1644@TK2MSFTNGP09...

Sbell

unread,

Jan 10, 2003, 1:29:31 PM1/10/03

to

Paul,

You're right... Very Fast, Very Slick...

thanks..

steve

"Paul" <new...@surfbest.net> wrote in message
news:#nt3XsDuCHA.1640@TK2MSFTNGP09...