Posts by Ponsonby

  • Prevent Deleting Sheet

    As well as disabling Delete Sheet on the Edit menu, to disable the worksheet tab pop-up menu :-


    Private Sub Worksheet_Activate()
    CommandBars("Ply").Enabled = False
    End Sub


    Private Sub Worksheet_Deactivate()
    CommandBars("Ply").Enabled = True
    End Sub

  • Disable Setup Button On Print Preview

    You are correct that the PrintPreview Method in the Help file does not mention any argument.


    However, a search for PrintPreview produces only 3 topics :-


    PrintPreview Method
    PrintPreview Method Example
    Methods with New Arguments


    The 3rd topic states that PrintPreview has an argument called EnableChanges

  • Disable Setup Button On Print Preview

    I took no offense to anything posted in this thread.
    Perhaps my last post could have been a bit more delicate - I tend to be a bit abrupt.


    The answer to the question posed in this thread is really not so difficult to find.


    A search for "PrintPreview" in VBE Help shows that the PrintPreview method has one argument called "EnableChanges".
    It doesn't take a great deal of logical deduction to set the argument to False, and then run it to see what it does.

  • Disable Setup Button On Print Preview


    Why don't you actually try my suggestion, rather than just dismissing it because you think you know what it does?


    It does not disable Print Preview.


    It opens Print Preview with SetUp and Margins disabled.

  • VBA: Delete Rows

    "It occurs to me that the For..Next and Find approaches could be improved by using Union for the found ranges and then just one delete of the combined range at the end of the routine. Not sure how much of an impact this would have, just thinking out loud. Perhaps I'll test for that too."


    Yes, of course using Union would probably be an improvement (depending upon the make-up of the data), but I don't suggest you spend time checking.
    The For...Next is a no-no, plus Excel does not delete a non-contiguous range (whether set by Union or whatever) in one step - it loops through the range one area at a time and deletes each area one at a time.
    I have pointed this out previously :- “… suffers from the usual problems of looping – that is, either deleting one row at a time or if the Union method is used, deleting each non-contiguous area one at a time”
    Find suffers from the same problems (but of course is much better than For...Next).


    "Have you tested on various sorts of data? For example, is the formula approach better with numeric data or text? Or is it just always better"


    Can't think of any reason why the type of data (numeric or text) would make a difference. The formula is merely looking for a particular value. Am only theorising - would be nice if you could demonstrate that it could make a material difference.

  • VBA: Delete Rows

    “I took your suggestion of alternate rows filled with "A" and then "B" and copied down, then looked for a randomly placed text string. (I also looked for a number within text and numbers within numbers (alternate rows of "234" and "567").”


    No, this was not what I did.
    (Do you mean that you were looking only for 2 rows in every 567 – i.e. 35 out 10000 ?
    Also, I don’t follow what you mean by “10 search values” and “100 search values”)


    What I did was to fill all rows alternatively with “A” & “B”, then to identify and delete all rows containing “A” - that is, every other row – the “worst case”.


    With the Formula/Sort method, the run-times are affected very little by the number of non-contiguous areas to be deleted – the difference between having to identify and delete one row or having to identify and delete 5000 non-contiguous rows is very little.
    For the Filter method, the difference is substantial


    Also, with the worst case, try running the Filter/SpecialCells method on 17000 total rows (that is, having to delete every other row).
    It won’t work since the SpecialCells method fails for this number of cells when Excel needs to do some kind of calculation.
    I previously said that the Formula/Sort method would also fail, but in fact it will work for any number of total rows and any number of rows for deletion (up to the worksheet’s total rows, of course).


    Edit :
    Also, I haven't tested it but know that the Find method will also take substantially longer for the worst case.

  • VBA: Delete Rows

    Thanks for your file, but I think the basis used for your testing is not appropriate (and too complex).


    How many tests of random data would you consider to be necessary before a reliable conclusion could be reached?


    We know what increases run-times, so why not just test based on worst case scenarios?


    In my last post, I don’t think the run times based on the scenarios set out lend themselves to a lot of different conclusions or interpretations.


    The difference between run-times of 1 second versus 2 minutes and 58 seconds is not small.

  • VBA: Delete Rows

    To check the run times of the Filter macro versus the Formula/Sort macro, I copied the OP’s data down to row 18,011 and recorded the run times with :-


    Code
    Dim t As Date
t = Now()
CODE
MsgBox Format(Now() - t, "hh:mm:ss")


    I’m using Excel 2000 and the run-times were :-


    Filter : 43 seconds
    Formula/Sort : 2 seconds


    Times, of course depend upon the make-up and the size of the data.
    The run-times for the Filter method will depend upon how many non-contiguous areas there are in the range to be deleted.
    With the Formula/Sort method, there is only one area to be deleted.


    Also, deleting areas near the top of a list takes longer than deleting areas near the bottom.
    With the Formula/Sort method the area to be deleted is at the end.


    Both methods will fail completely if the SpecialCells range consists of more than 16,000 (approx) rows (perhaps this is not so in later versions of Excel?).
    So the Filter macro will fail when the total rows of data exceed the limit, whereas the Formula/Sort macro can handle larger data and will fail only when the rows to be deleted exceed the limit.


    If the limit is likely to be exceeded, then the Find method would appear to be the obvious alternative. But, the Find method suffers from the usual problems of looping – that is, either deleting one row at a time or if the Union method is used, deleting each non-contiguous area one at a time.

    However, with large amounts of data that exceed the SpecialCells limit, there may be better ways than Find.
    For example :-
    Identify the rows to be deleted via a formula
    Sort the data to put the rows to be deleted at the bottom
    Via Find (or via a formula), identify the first row of the range to be deleted and set the range (i.e SpecialCells method not necessary)
    Delete the range


    I did two more tests on the two methods.
    I set up what should be a worst case scenario for the Filter method (and for the Find method) :-
    In cells G1:P1 entered “A”
    In cells G2:P2 entered “B”


    For the first test the data was filled down to G10000:P10000 and down to G15000:P15000 for the second test.


    The results for deleting rows that contained “A” in column I were :-


    10000 rows
    Filter : 1 minute 15 seconds
    Formula/Sort : less than 1 second


    15000 rows
    Filter : 2 minutes 58 seconds
    Formula/Sort : 1 second


    What was your code using Find?