# 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.

• ## Print To Pdf - Auto Save As

Re: Print To Pdf - Auto Save As

Code
``````Application.ActivePrinter = "Adobe PDF on Ne05:"

activesheet.printout printtofile:=true, prtofilename:="mypath"``````
• ## Print To Pdf - Auto Save As

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

Code
``````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...?)

• ## Sum of X Largest Values

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. ...

=SUMPRODUCT((RANK(A1:A30,A1:A30,TRUE)>(COUNT(A1:A30)-15))*A1:A30)

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

• ## Calculate Times Residing in One Cell

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 ('-')
Finish

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
http://www.ozgrid.com/Excel/date-time-calculations.htm

• ## Remove Preceeding Zeros

Re: Kill Zeros From Number

a sub with a sense of humor?

now for the curtain call:

Code
``````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
if
end if
my_unnecessary_ifs.ignore
end sub``````
• ## Remove Preceeding Zeros

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....)

• ## Collect Formula From User & Add to Range

Re: Inputbox Method To Get A Formula

Very cool.

I did not realize the difference between and Excel input and a VB input.

Learn new stuff every day here.

Thanks to both of you.

• ## Skip Column In Select Case

Re: Skip Column In Select Case

Try this:

• ## Run Input Text Box & Save To Sheet

Re: Run Input Text Box &amp; Save To Sheet

Jim,

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

Code
``````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.

• ## Protect Multiple Workheets

Re: Changing Multiple Worksheets With One Action

Excellent tip.

Thanks Mike?...err...Mik?....err....*Mik*. (hard to tell.)

Thanks Again!

• ## Protect Multiple Workheets

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.

• ## Sum Of Postal Code By Region(canada)

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:

=SUMPRODUCT(ISNUMBER(FIND(A12,\$C\$3:\$C\$8))*\$D\$3:\$D\$8)

in B12 and copy down

• ## Optimize VBA Code

Re: Optimizing My Code

Without knowing what is on this sheet...

l

Code
``````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

l

Code
``````lastrow = Cells(Rows.Count, "A").End(xlUp).Row - 2
Rows(lastrow & ":" & Rows.Count).clear``````
• ## Hide Columns By Date

Re: Hiding Columns By Date

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

Code
``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.

• ## Hide Columns By Date

Re: Hiding Columns By Date

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

Code
``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.

• ## Formula To Sum Three Columns

Re: Formula To Sum Three Columns

Yep - quite right Cringe (AND SHG!)

I Reding and tping toooo fst tday. LOL!

• ## Hide Columns By Date

Re: Hiding Columns By Date

No - you are not being a pain at all.

It is quite right to point out better syntax.

No worries - thanks.

• ## Formula To Sum Three Columns

Re: Formula To Sum Three Columns

mmmmm - don't think so shg...

...but if there is no additional array, then the parenthesis are probabaly unnecessary.

• ## Formula To Sum Three Columns

Re: Formula To Sum Three Columns

=SUMPRODUCT((A1:A100="customername")*(B100:D100))

• ## Hide Columns By Date

Re: Hiding Columns By Date

Assuming all column headers are in row 1.

can you make something like this work:

Code
``````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.