The flashing was stopped using Application.ScreenUpdating.
Setting Application.ScreenUpdating to false, helps increase the performance of your code.
Printable View
The flashing was stopped using Application.ScreenUpdating.
Setting Application.ScreenUpdating to false, helps increase the performance of your code.
True, but likely less flashing as well, as not only that, but the revised code does up to two deletes on the entire range, whereas the original does up to x deletes (which can be a large number) up until a blank cell. This also has the potential for significant performance implications. :)
Your code is faster by about 1.5 seconds. I knew that the maximum rows would be 250, so I was not too worried about performance. If the total rows were in the thousands, my solution would have been a poor/slow solution.
You code could be improved in the following ways:
and deleteCode:.Range("D5 : D65536").AutoFilter Field:=1, _
.Range("D6 : D65536").SpecialCells(xlVisible).EntireRow.Delete
As in most cases in Excel, there are several ways of solving the problem. Each one teaches us interesting things about Excel.Code:If CBool(InStrB(.Range("d6").Value, "vbaexpress")) Then _
.Range("d6").EntireRow.Delete
Correct, avoid the conditional! That was a generic approach for data beginning in row 1 with no header, but it need not apply here. :)
Are you missing the criteria? How about the following:
Also, when one says 1.5 seconds as opposed to what? Is that 100% or 2%, etc... :)Code:Private Sub RemoveVBAExpress2()
' Macro recorded by Nate Oliver
Application.ScreenUpdating = False
With Worksheets(1)
.Range("D5:D65536").AutoFilter Field:=1, _
Criteria1:="=*vbaexpress*"
On Error Resume Next
.Range("D6:D65536").SpecialCells(xlVisible).EntireRow.Delete
On Error GoTo 0
.AutoFilterMode = False
End With
Application.ScreenUpdating = True
End Sub
How would one know this, old copy or is there a field of some sort?Quote:
Originally Posted by Dreamboat
I'd just download the two and use conditional formatting with a match function for this.
Since my internet explorer is broken right now, and I'm missing your passwords, I can't duplicate my advice, but if you post a sample of two downloads, I can show you. :)
Edit: I think you'd need to maintain a DB of referrers-to-date to effectively determine first time referrers eh.
Okay, let's check this one out. :)
Can somebody take that most recent version and "genericize" it for me? I did post at our host's forums and at least one person is interested in it, but I'd like them to easily be able to edit the code, so comments would be REALLY helpful. :)
Dreamboat,
Thanks for posting the file for me. I was getting bleary-eyed and very cranky by the time I sent you the file. I had been up since 4am after only four bad hours of sleep.
Now I feel much better. It is surprising what a good night of sleep will do.
Nate's comment about keeping a list of the referrers is probably what you want. From some other posts you have done, it looks like that is what you are trying to do. Is that correct? If not, do you want this spreadsheet to do that? Or has someone already done that?
For me, I love it the way it is now. I mean...if someone *falls off the list* as a referrer, I'd like to know if they come back on, so I don't think I need an *all-time* referrers list. Suppose Joe Schmoe links to us in a newsletter or something. Then that dies away. If they link to us in a newsletter a year later, we'd want to see that. :)