Posts by Todkerr

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Re: Print To Pdf - Auto Save As

    Does the box caption say:

    "print to file"

    with a message saying:

    "output filne name:"

    if yes, then the box is related to P2F.

    no one likes sendkeys....but will

    Sub Macro3() 
        Application.ActivePrinter = "Adobe PDF on Ne05:" 
    SendKeys ("c:\mypath...")
    SendKeys ("~")
        ActiveWindow.Worksheets("Sheet1").PrintOut From:=1, To:=1, Copies:=1, ActivePrinter:="Adobe PDF on Ne05:", Collate:=True 
    End Sub

    Make a difference (will probably still see printing dialog - but no user input...?)

    Re: Sum of X Largest Values

    Jindon's formula will work great - but if you need to define top 50 or top 150 numbers, or if you wanted the number to change as a variable etc. ...


    might be easier to manage. (where "-15" is changed to -n for the sum of the n largest numbers.)

    Re: Time Range In A Single Cell

    Simplest solution:

    1) Highlight the data you want to convert
    2) in excel: data>text to columns...
    3) choose 'delimited' (next>)
    4) choose 'other' and type in a hyphen ('-')

    This should give you a start time and an end time in two different columns. Subtract the start time from the end time in a 3rd column (simple formula "=b1-a1") where b1 has the end time and a1 has the start time. Convert this formula to a time format like "13:30"

    You can sum times into hours totaling greater than 24 by using a time format like "37:50:55"
    further help on time math can be found at

    Re: Kill Zeros From Number

    a sub with a sense of humor?

    now for the curtain call:

    Sub lingual (with cheek)
    dim wit as long ' as in Tod's code is too long
    with wit 
    wit = wit/2 '  about half the time, anyway
     end if
    end sub

    Re: Kill Zeros From Number

    there is probabaly a more efficient way to do this...but this will work, I think (highlight the cells you want to change....)

    Re: Skip Column In Select Case

    Try this:

    Re: Run Input Text Box & Save To Sheet


    See if you can make a version of this work for you:

    Sub beyond_reason()
    Dim Reason As Variant
    Reason = InputBox("Why did you revise this?")
    Sheets("revision").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = Reason
    Sheets("revision").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = Now()
    End Sub

    This pastes the reason and time of change into column A and B on "revision" sheet.

    Re: Changing Multiple Worsheets With One Action

    Protecting could be done with:

    Unprotecting could be done with:

    Sorry - gotta go - perhaps someone else could help with the spell check.

    Re: Sum Of Postal Code By Region(canada)

    Of interest to me since CanadaPost appears to have LOST MY EBAY PURCHASE!!!!! LOL

    if you want sums then maybe a different approach

    edited to remove unnecessary pareth:


    in B12 and copy down

    Re: Optimizing My Code

    Without knowing what is on this sheet...


    lastrow = Cells(Rows.Count, "A").End(xlUp).Row - 2 
        Rows(lastrow & ":" & Rows.Count).Delete Shift:=xlUp

    Looks like it could be time consuming....

    can you accomplish the same thing faster with


    lastrow = Cells(Rows.Count, "A").End(xlUp).Row - 2 
        Rows(lastrow & ":" & Rows.Count).clear

    Re: Hiding Columns By Date

    But correct me if I am wrong - and I often am!!!!

    Range("A1", .Cells(1, Columns.Count).End(xlToLeft)).Address)

    will return a1 to the last visible cell, not the last used cell.

    If A1-H1 is January though July - and July is hidden becuase it is now June....and saved that way...

    Next month in July - your sub won't unhide July because H1 will not be part of the for loop.

    Maybe I am missing something. I guess I'm assuming that the current month will be the last visible column with data in Row 1. If this is the case....then you need to force eval of the hidden rows - if this is not the case then your sub will work.

    Re: Hiding Columns By Date

    and....not to be a pain back Reafidy -

    Your sub omits the line:

    Cells.EntireColumn.Hidden = False

    I believe that if this is ommitted, previously hidden columns that become current/past as time progresses will not be made visible by your sub.

    Re: Hiding Columns By Date

    Assuming all column headers are in row 1.

    can you make something like this work:

    Sub auto_open()
    Cells.EntireColumn.Hidden = False ' unhide all columns when you start
    Range("A1", Cells(1, Columns.Count).End(xlToLeft)).Select
    For Each x In Selection.Cells
        If x.Value > Now Then x.EntireColumn.Hidden = True
        Next x
    End Sub

    You might have to play a touch with the date formatting to get the current month to show properly.