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.

Posted on January 13th, 2010 by Bastien
Filed under: General, how to | Comments Off

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

Posted on August 13th, 2009 by Bastien
Filed under: General, how to | 3 Comments »

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

Posted on November 19th, 2008 by Bastien
Filed under: General | 5 Comments »

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

Posted on October 27th, 2008 by Bastien
Filed under: General | Comments Off

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 & " [...]

Posted on October 23rd, 2008 by Bastien
Filed under: General | 5 Comments »

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

Posted on October 21st, 2008 by Bastien
Filed under: ASAP Utilities, General, how to | Comments Off

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

Posted on September 18th, 2008 by Bastien
Filed under: ASAP Utilities, General | Comments Off

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

Posted on September 17th, 2008 by Bastien
Filed under: ASAP Utilities, General | 1 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 [...]

Posted on September 16th, 2008 by Bastien
Filed under: ASAP Utilities, General | 2 Comments »

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

Posted on June 3rd, 2008 by Bastien
Filed under: Excel 2007 info, General | Comments Off