×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

VBA to conditionally delete rows - need faster method

VBA to conditionally delete rows - need faster method

VBA to conditionally delete rows - need faster method

(OP)
I have some code containing a for next loop, which loops through the rows in my range in reverse from bottom to top, and conditionally deletes entire rows from my range, if condition 1, 2, 3, 4 or 5 are true. There is about 4,000 rows in my range of data. This section of the code is taking about 10 minutes to run, and I need to speed things up. Here is the pertinent section of code:

CODE

For i = LastRow To 3 Step -1
  If (Cells(i, 4).Value <> "Broadband Service" And Cells(i, 4).Value <> "Business Service" And Cells(i, 4).Value <> "Residential Service" And Cells(i, 4).Value <> "Video Service") _
  Or (Cells(i, 8).Value = "Upgrade" And (Cells(i, 18).Value = True Or Cells(i, 4).Value <> "Broadband Service")) _
  Or ((Cells(i, 4).Value = "Business Service" Or Cells(i, 4).Value = "Residential Service") And (Cells(i, 5).Value <> "ACCMLB" And Cells(i, 5).Value <> "ACCRES" And Cells(i, 5).Value <> "ACCSLB")) _
  Or (Cells(i, 4).Value = "Broadband Service" And (Left(Cells(i, 5).Value, 3) <> "HSI" Or Cells(i, 5).Value = "HSICRED" Or Cells(i, 5).Value = "HSI100CR" Or Cells(i, 5).Value = "HSIEMP" Or Cells(i, 5).Value = "HSIINST") _
  Or (Cells(i, 4).Value = "Video Service" And (Cells(i, 5).Value <> "BASIC" And Cells(i, 5).Value <> "DTVEXPAND" And Cells(i, 5).Value <> "DTVEXPANDUP" And Cells(i, 5).Value <> "DTVLOCALPLUS") _
  Then Cells(i, 4).EntireRow.Delete
Next 

Is there a faster way to accomplish the same thing without looping through each row individually?

Thanks,
Kevin

RE: VBA to conditionally delete rows - need faster method

(OP)
I was actually able to find what I think is a good solution to this problem in another discussion forum. Here is the updated code, which sets a range of just the rows that meet the needed conditions, then deletes that range, leaving behind the remaining rows:

CODE

For i = LastRow To 3 Step -1
  If (Cells(i, 4).Value <> "Broadband Service" And Cells(i, 4).Value <> "Business Service" And Cells(i, 4).Value <> "Residential Service" And Cells(i, 4).Value <> "Video Service") _
  Or Cells(i, 8).Value = "Upgrade" And (Cells(i, 18).Value = True Or Cells(i, 4).Value <> "Broadband Service") _
  Or (Cells(i, 4).Value = "Business Service" Or Cells(i, 4).Value = "Residential Service") And (Cells(i, 5).Value <> "ACCMLB" And Cells(i, 5).Value <> "ACCRES" And Cells(i, 5).Value <> "ACCSLB") _
  Or Cells(i, 4).Value = "Broadband Service" And (Left(Cells(i, 5).Value, 3) <> "HSI" Or Cells(i, 5).Value = "HSICRED" Or Cells(i, 5).Value = "HSI100CR" Or Cells(i, 5).Value = "HSIEMP" Or Cells(i, 5).Value = "HSIINST" Or Left(Cells(i, 5).Value, 4) = "HSIL") _
  Or Cells(i, 4).Value = "Video Service" And (Cells(i, 5).Value <> "BASIC" And Cells(i, 5).Value <> "DTVEXPAND" And Cells(i, 5).Value <> "DTVEXPANDUP" And Cells(i, 5).Value <> "DTVLOCALPLUS" And Cells(i, 5).Value <> "DTVLOCALPLUSUP" And Cells(i, 5).Value <> "DTVSUPREME" And Cells(i, 5).Value <> "DTVSUPREMEUP" And Cells(i, 5).Value <> "RFOVERLAY") _
  Then If rng Is Nothing Then Set rng = Cells(i, 4) Else Set rng = Union(rng, Cells(i, 4))
Next

If Not rng Is Nothing Then rng.EntireRow.Delete 

Thanks,
Kevin

RE: VBA to conditionally delete rows - need faster method

I would create my own UDF where I can pass a few parameters (values from columns 4, 5, 8, and 18) and return a string of either 'Delete' or 'Keep', use a helper column to evaluate my parameters, and then filter this column and delete all rows with 'Delete'.

Just a suggestion.... smile


---- Andy

There is a great need for a sarcasm font.

RE: VBA to conditionally delete rows - need faster method

There'a a lot of referring to the sheet in those macro snippets, it might be worth trying something along these lines (I can't test easily):

CODE --> vba

Dim Rng As Range
Set myRng = Range("A1:R" & LastRow)
rngVals = myRng.Value
For i = 3 To LastRow
  If (rngVals(i, 4) <> "Broadband Service" And rngVals(i, 4) <> "Business Service" And rngVals(i, 4) <> "Residential Service" And rngVals(i, 4) <> "Video Service") _
  Or rngVals(i, 8) = "Upgrade" And (rngVals(i, 18) = True Or rngVals(i, 4) <> "Broadband Service") _
  Or (rngVals(i, 4) = "Business Service" Or rngVals(i, 4) = "Residential Service") And (rngVals(i, 5) <> "ACCMLB" And rngVals(i, 5) <> "ACCRES" And rngVals(i, 5) <> "ACCSLB") _
  Or rngVals(i, 4) = "Broadband Service" And (Left(rngVals(i, 5), 3) <> "HSI" Or rngVals(i, 5) = "HSICRED" Or rngVals(i, 5) = "HSI100CR" Or rngVals(i, 5) = "HSIEMP" Or rngVals(i, 5) = "HSIINST" Or Left(rngVals(i, 5), 4) = "HSIL") _
  Or rngVals(i, 4) = "Video Service" And (rngVals(i, 5) <> "BASIC" And rngVals(i, 5) <> "DTVEXPAND" And rngVals(i, 5) <> "DTVEXPANDUP" And rngVals(i, 5) <> "DTVLOCALPLUS" And rngVals(i, 5) <> "DTVLOCALPLUSUP" And rngVals(i, 5) <> "DTVSUPREME" And rngVals(i, 5) <> "DTVSUPREMEUP" And rngVals(i, 5) <> "RFOVERLAY") _
  Then If Rng Is Nothing Then Set Rng = Cells(i, 4) Else Set Rng = Union(Rng, Cells(i, 4))
Next i

If Not Rng Is Nothing Then Rng.EntireRow.Delete 

RE: VBA to conditionally delete rows - need faster method

kskinne,

If you check back here, did any of the posts get you headed in the right direction? Did you work out or find a solution? Would be nice to see a follow-up at least giving a clue as to what you ended up doing if anything.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

RE: VBA to conditionally delete rows - need faster method

You ought to be able to put an AutoFilter on your table to make visible the rows you want to delete.

Then Select the visible rows and Delete in one operation, without a loop.

Once you have perfected your filter selections, turn on your macro recorder and record...
1) turning on the filter,
2) making the filter selections,
3) selecting the visible rows,
4) deleting the selection and
5) clearing all filters (show all data in your table)

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close