General - ASAP Utilities Blog

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

General

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

Nov 19, 2008 Bastien 5 Comment

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 of the shortcuts in ...

General

Prepare a document before sending it to others

Oct 27, 2008 Bastien 0 Comment

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 method to use ...

General

Weird looping through cells in a range object

Oct 23, 2008 Bastien 5 Comment

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 address Debug.Print rngCel.Address & " ...

ASAP Utilities General How to how to with ASAP Utilities

Unhide all columns and rows

Oct 21, 2008 Bastien 0 Comment

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 unhide, you can also use this tool ...

ASAP Utilities General

Keep leading and remove only the trailing spaces

Sep 18, 2008 Bastien 0 Comment

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

ASAP Utilities General

Add trailing zeros

Sep 17, 2008 Bastien 1 Comment

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 to normalize the numbers so they would all be ...… Read more...

ASAP Utilities General

Replace formulas with their calculated results in multiple sheets

Sep 16, 2008 Bastien 2 Comment

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

General

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

Jun 3, 2008 Bastien 0 Comment

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

General

Google Desktop Search slows down Excel

Dec 20, 2007 Bastien 0 Comment

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

General

Problem with copying filtered data in Excel 2007 + workaround

Oct 25, 2007 Bastien 49 Comment

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

ASAP Utilities General

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

Oct 23, 2007 Bastien 0 Comment

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

ASAP Utilities General

Macro: Export each column to a separate sheet

Oct 9, 2007 Bastien 0 Comment

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

General

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

Jul 10, 2007 Bastien 0 Comment

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

ASAP Utilities General

Fix the slow startup of Excel 2007

Jul 9, 2007 Bastien 61 Comment

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

General

Microsoft: Description of the undiscovered tips about Excel

Jun 21, 2007 Bastien 2 Comment

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

General

How to increase the number of undo levels in Excel

May 28, 2007 Bastien 0 Comment

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

General

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

Apr 24, 2007 Bastien 1 Comment

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

General

Remove comment spam from your WordPress database

Apr 19, 2007 Bastien 3 Comment

I have been getting a lot of comment spam on this blog. I've just removed over 20.000 comments from the database. Apparently WordPress still stores the comment if you mark it as spam. I haven't looked in the code if it uses this for some clever techniques to detect future spam. Right now I just ...

General

Remove all rules in Outlook at once

Feb 9, 2007 Bastien 18 Comment

Today my Outlook was messed up a bit and several rules didn't work correctly any more. I had a good backup but I found out that if I imported the rules-backup, the original rules were not replaced but instead the imported rules were added to the list. So you need to remove all all ...

General

Excel 2007: Show the Developer tab or run in developer mode

Feb 5, 2007 Bastien 0 Comment

When you install the new Office 2007 the first thing you want to do is show the developer tab. You should display the Developer tab or run in developer mode when you want to write macros, run macros that you previously recorded, or create applications to use with Microsoft Office programs. Click the Microsoft Office Button Button ...

General

Help, my columns are displayed backwards

Jan 3, 2007 Bastien 0 Comment

Just got a question that migh be interesting to show here about columns that are displayed backwards. The columns are in reverse alphabetical order and start at the upper right hand corner. The row numbers are on the right side of excel instead of the left. The Problem: Recently, when I opened Excel, the columns ...

General

Tip: Insert multiple rows at once

Nov 13, 2006 Bastien 3 Comment

Inserting multiple rows "the Excel way" If you want to insert multiple rows at once the standard procedure is to first select multiple rows and then use the "Insert" command. For example if you want to insert 10 rows between A1 and A2, you first have to select rows 2:11 and then insert the rows. Inserting multiple rows ...

General

Color a cell by just clicking on it

Oct 1, 2006 Bastien 1 Comment

make a cell red when you double-click on it, and when you double-click on it again the red color is removed

Last week I got a question from Amit: I use the asap utilities a lot and it is great help. My question is regarding cell coloring. I'd like to be able to assign only two colors to a cell. For example, say if you select a cell once (or click on it once), it turns Red. ...

General

Screenshots of the different Excel versions

Sep 27, 2006 Bastien 0 Comment

I have always wondered how Excel looks in other countries. In my office we use both the international and the Dutch version(s) of Excel. Today I have created a new page on the main website displaying screenshots of the various Excel versions in different countries. At this moment only international and Dutch ranging from Excel 97 ...