Insert random dates and times in Excel

Example - Random dates and times

We just received the following question: "Would it be possible to add random date and times?" Yes, this can (already) be done with just Excel. We might add a tool for it in ASAP Utilities that will ask you for the input in the future, but it isn't hard to do yourself with the combination […]

How to: Create a bulleted list in Excel

Insert a bullet list in MS Word

Unlike Word, Excel doesn't have a similar option to insert a bulleted list. However, you can easily create a similar list in Excel by using a so called "custom number format". That might sound a bit difficult but it really isn't. Just select the list and right-click on any cell in your selection: Then choose […]

Happy Holidays

Merry Christmas!

Happy Holidays and Merry Christmas! Lots of love and happiness for you, your co-workers, family and friends. And if you don't celebrate Christmas, have a great weekend!

User question: How to send this sheet to an e-mail in Excel 2010?

Sent this Sheet

"Hi Bastien, I am a great admirer of you ASAP-utilities. Great work. I have a little problem with Excel 2010, that I am hoping that you might have a solution to. We have just switched over to the excel-2010 from excel-2003. In the 2003 there was a button to send this sheet to an e-mail. […]

User question: How to enter the same value on multiple sheets (or in multiple cells at once)

Press control+enter to enter the same value in all selected cells

Today we received the following email: Let me start by saying how wonderful I think your program is. It saves me a lot of time. I just created 100 sheets and named them from a list using ASAP -great! I then inserted the name of each sheet into a cell within the sheet using ASAP […]

Happy New Year!

May the New Year bring you health, happiness, and all other good things!

I wish you all a wonderful new year with lots of health, happiness and prosperity! May the New Year be a good year to you and yours. And we'd like to thank you for your suggestions, enthusiasm and encouragement throughout the past 12 years. We've planned several new exiting and improved tools for ASAP Utilities […]

How to: Check the spelling on multiple or all sheets

The spelling check is complete for the selected sheets

Last week I received the following question: How we can spell check on all sheets. Can you add this in next version/update? Well, you can already do this in Excel. Just select all or multiple sheets and then when you start the Spelling (F7) the spelling check will take place on the selected sheets.

Macro: Keep only ASCII characters 32-127

Last week I received the following question: I get sent Excel files from various vendors. I have to convert these to SAS datasets that get moved to a UNIX system. The SAS datasets can only contain ASCII 32-127. So I need to remove these from the Excel file. You can use the "Text » Advanced […]

List of keyboard shortcuts for Word 2002, Word 2003, and Word 2007

A bit off-topic but because working with shortcuts is often so much faster. I believe that shortcuts are are preferred when it comes to speed/productivity and reducing the chances on RSI. Microsoft: List of keyboard shortcuts for Word 2002, Word 2003, and Word 2007 http://support.microsoft.com/kb/290938 Just in case you weren't aware, we have a list […]

Prepare a document before sending it to others

When you send your Excel workbook to other people, sometimes you want to protect or hide certain parts. For example the formulas you used or temporary data in hidden columns. You can do this by using Excel's worksheet protection, however you should know that this protection is easily broken and not very secure. A better […]

Weird looping through cells in a range object

Below is something that I experienced a while ago and was beyond my logic. Setting a range and looping through all cells in it didn't work initially as expected. Sub WeirdCelLooping()      Dim rngCel                                       As Range      Dim rngSelection                                 As Range       Range("A1:G10").Select       ' Doesn't work correctly:      For Each rngCel In Selection.Columns(1)            ' both rngCel and the selection have now the same […]

Unhide all columns and rows

A short macro that will unhide all columns and rows in order to make all data visible again. Might be useful in some cases. Sub UnhideAllColumnsRows() ' unhide all columns and rows on the current worksheet Cells.EntireColumn.Hidden = False Cells.EntireRow.Hidden = False End Sub If you want more control on which rows and/or columns to […]

Keep leading and remove only the trailing spaces

Jeff recently asked: I really like your product but just came upon a capability it is missing: Delete trailing spaces (but keep all other spaces). I expect it would be simple enough to add; would you? I never expected anyone to need a tool to only remove the trailing spaces but he needs it on […]

Add trailing zeros

example-fill-ending-zeros.gif

Max asked the following: Can the "Fill > fill up with leading zero's" option under be made to work on leading or trailing zeros? For instance, if I have three numbers: 1 12 123 I would run the option to fill them with trailing zeros so they would look like: 1000 1200 1230 We want […]

Replace formulas with their calculated results in multiple sheets

It's time to update this blog more often. I will do this by writing about questions I answered by email and that might be interesting for others too. Tom asked: Formula » Convert formulas to their values: I was hoping you could tell me if there is there a way to do this for all […]

Excel 2007 sheet copy: Application-defined or object-defined error

excel-2007-application-or-object-defined-error.png

When you use the code sheet.copy normally a new workbook is created with a copy of that particular sheet. However in Excel 2007 (SP1) if the original workbook contains a macro when you run the code the first time you (might) get the following error: I could only reproduce this error when the original workbook […]

Google Desktop Search slows down Excel

Google Desktop Search

Do you read the website "Daily Dose of Excel"? If not, it is a very interesting website about Excel. Frequently updated and many interesting discussions. Today I want to share an article with you: This week Dick Kusleika posted a very interesting article where he quoted Charles Williams from DecisionModels.com. Charles Williams is an absolute […]

Problem with copying filtered data in Excel 2007 + workaround

I just got an email that says you cannot copy only the filtered data in Excel 2007. If you use the autofilter to make a selection and then you only want to copy the filtered rows, Excel 2007 copied everything, even the hidden rows. This is different from the way Excel 2000-2003 works. You can […]

Protect sheets: The EnableSelection property is not saved + Excel update fix

Protect sheets: The EnableSelection property is not saved + Excel update fix

Yesterday I got the following question: "When using the 'protect multiple sheets at once” and checking only the 'select unlocked cells” checkbox I find that once I save and reopen the sheets now behave as if both the 'select unlocked cells” as well as the 'select locked cells” seems to be in effect as I […]

Macro: Export each column to a separate sheet

Last week I got a question from Danielle: "I have a spreadsheet with 75 columns each containing between 48 to 50 rows. I would like to be able to export each column to either a separate sheet or a separate file. Sort of like the export worksheets as separate files function. Does this function already […]

Excel file loads slowly the first time that it is opened in Excel

General Excel question: Recently I have been getting this same question by several customers. If they open a file in Excel, the first time it takes a very long time. If they then save the file and open it again it loads much faster. This occurs when you open an Excel file that has been […]

Fix the slow startup of Excel 2007

Excel 2007 Splash Screen

Recently I have been receiving a few emails about people telling me that their Excel 2007 would startup very slowly with ASAP Utilities installed. The delay also occurred without ASAP Utilities if they click on an Excel file from within the Explorer: If I click on an xls file Excel opens with a blank window which […]

Microsoft: Description of the undiscovered tips about Excel

Microsoft has written an article on how some things in Excel can be done without the use of macro's. An interesting list. However I remember that back in 1999 when I use their approach to "Exclude duplicate items in a list" I found out that this was limited to approximately 1000 unique items. This article […]

How to increase the number of undo levels in Excel

How to increase the number of undo levels in Excel by changing the registry

By default, Excel keeps track (allocates RAM) of 16 "undo" levels. Undo levels provide an undo history for most actions that you perform in Excel. You can change the number of undo levels by adding an entry in the Microsoft Windows registry. If the undo history is set to zero, no memory is allocated for […]

Range.Value = Range.Value, isn't it?

There are several ways to copy information from one sheet to another. Today I discovered an inconsistancy while using something similar like the following code: Sheet1.Range("A1:A100").Value = Sheet2.Range("A1:A100").Value The above code does not work correctly if the cells in sheet 2 are formatted in currency- or accounting format. In that case Excel does not copy […]

Page 1 of 212