Consulting

Page 2 of 2 FirstFirst 1 2
Results 21 to 29 of 29

Thread: Get Web Stats with Webalizer and Analyze

  1. #21
    VBAX Regular
    Joined
    May 2004
    Location
    Seattle, WA
    Posts
    24
    Location
    The flashing was stopped using Application.ScreenUpdating.
    Setting Application.ScreenUpdating to false, helps increase the performance of your code.

  2. #22
    VBAX Regular NateO's Avatar
    Joined
    Jun 2004
    Location
    Minneapolis, MN
    Posts
    90
    Location
    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.
    Last edited by NateO; 06-16-2004 at 09:12 AM. Reason: Clarification
    Regards,
    Nate Oliver

  3. #23
    VBAX Regular
    Joined
    May 2004
    Location
    Seattle, WA
    Posts
    24
    Location
    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:
    .Range("D5 : D65536").AutoFilter Field:=1, _
    .Range("D6 : D65536").SpecialCells(xlVisible).EntireRow.Delete
    and delete

    If CBool(InStrB(.Range("d6").Value, "vbaexpress")) Then _
    .Range("d6").EntireRow.Delete
    As in most cases in Excel, there are several ways of solving the problem. Each one teaches us interesting things about Excel.

  4. #24
    VBAX Regular NateO's Avatar
    Joined
    Jun 2004
    Location
    Minneapolis, MN
    Posts
    90
    Location
    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:

    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
    Also, when one says 1.5 seconds as opposed to what? Is that 100% or 2%, etc...
    Regards,
    Nate Oliver

  5. #25
    VBAX Regular NateO's Avatar
    Joined
    Jun 2004
    Location
    Minneapolis, MN
    Posts
    90
    Location
    Quote Originally Posted by Dreamboat
    Now, y'all seem to be enjoying this so much... it might be REALLY cool if I could run it, and see in red text or fill or something, any NEW referrers from the previous run.
    How would one know this, old copy or is there a field of some sort?

    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.
    Regards,
    Nate Oliver

  6. #26
    Site Admin
    The Princess
    VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Okay, let's check this one out.
    ~Anne Troy

  7. #27
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    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.
    ~Anne Troy

  8. #28
    VBAX Regular
    Joined
    May 2004
    Location
    Seattle, WA
    Posts
    24
    Location
    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?

  9. #29
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    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.
    ~Anne Troy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •