Filed under: General on November 19th, 2008 by Bastien | 1 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 Excel listed too:
http://www.asap-utilities.com/excel-tips-shortcuts.php
PS.
If you know the shortcuts for the tools you use the most, you are not slowed down by searching for the option in the new Office 2007 ribbon the first period after you have upgraded to the new interface.
Filed under: General on November 19th, 2008 by Bastien | 1 Comment »
Filed under: ASAP Utilities on November 18th, 2008 by Bastien | 4 Comments »
While I'm working on the next big updated version (4.3), a few people have found some bugs in version 4.2.8 that I've now fixed in version 4.2.9.
More information and download of the pre-release:
http://www.asap-utilities.com/faq-questions-answers-detail.php?m=195
This version will probably be officially released next Thursday.
Filed under: ASAP Utilities on November 18th, 2008 by Bastien | 4 Comments »
Filed under: ASAP Utilities, how to on November 13th, 2008 by Bastien | No Comments »
Enno asked me the following:
"...
When I value-copy ranges where the If-function was used with a return value of "" (empty string), the cells are not blank, hence ISBLANK() = FALSE.
Could you add a feature to remove empty strings?
..."
Well, you can do this already with ASAP Utilities, although you cannot find it in the description of the tools (yet).
First select the cells and then run the following utility: Numbers » Convert "textnumbers" to numbers
As a side effect (bonus) this utility will also make Excel recognize such empty cells properly again.
Filed under: ASAP Utilities, how to on November 13th, 2008 by Bastien | No Comments »
Filed under: ASAP Utilities on October 30th, 2008 by Bastien | 3 Comments »
I got the following question from Jonathan this week:
...
Don't know if this can be done at the moment, perhaps you could advise...
I have a range of cells and I want to select ALL the cells/rows in the selection where the value changes. I can select the first cell where the value changes using 'Select Cells' (3) then 'Select cells in range where value changes' (11) but want it to do a bit more.
I will then insert blank rows where the value changes.
Hope you can help. Keep up the good work.
...
Inserting a blank row after the value changes can be done with the following utility (which I have always found it difficult to use a good name for): Format » Detect and visualize adjacent data/group changes
Select the column with the values/titles that change at some point and then start the following utility:
ASAP Utilities » Format » Detect and visualize adjacent data/group changes
Then choose "empty row" and press the "start" button:
Filed under: ASAP Utilities on October 30th, 2008 by Bastien | 3 Comments »
Filed under: General on October 27th, 2008 by Bastien | No 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 might be to create a new version of your workbook in which you replace all the formulas with their calculated results and in which all the hidden data is removed.
The following macro will automatically replace all formulas with their calculated results and removes the hidden columns and rows on the selected worksheets:
Read more...
Filed under: General on October 27th, 2008 by Bastien | No Comments »
Filed under: General on October 23rd, 2008 by Bastien | 5 Comments »
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 & " as part of " & Selection.Columns(1).Address
Next
' Doesn't work correctly:
Set rngSelection = Selection.Columns(1)
For Each rngCel In rngSelection
' both rngCel and the selection now still have the same address
Debug.Print rngCel.Address & " as part of " & Selection.Columns(1).Address
Next
' Does work correctly:
Set rngSelection = Range(Selection.Columns(1).Address)
For Each rngCel In rngSelection
' finally we can loop through each cell in the range
Debug.Print rngCel.Address & " as part of " & Selection.Columns(1).Address
Next
End Sub
Update October 24:
As stated in the comments below (thank!); the best practice to loop through a range is to explicitly add the .Cells at the end for the range:
Selection.Columns(1).Cells
rngSelection.Cells
Filed under: General on October 23rd, 2008 by Bastien | 5 Comments »
Filed under: ASAP Utilities, General on October 21st, 2008 by Bastien | No 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 from within ASAP Utilities.
Filed under: ASAP Utilities, General on October 21st, 2008 by Bastien | No Comments »
Filed under: ASAP Utilities on September 24th, 2008 by Bastien | 12 Comments »
Yesterday the new version of ASAP Utilities was officially released.
You can read the details in the newsletter or you can immediately proceed to the download page.
Besides several bugfixes, these are two new things:
Filed under: ASAP Utilities on September 24th, 2008 by Bastien | 12 Comments »
Filed under: ASAP Utilities, General on September 18th, 2008 by Bastien | No Comments »
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 data that is indented and therefore the leading spaces should remain.
As you probably know, the TRIM function will remove all leading and trailing spaces from a value. Well, Excel VBA also has the LTRIM and RTRIM functions which will either remove all the spaces from the left (leading) or from the right (trailing).
The following macro will do the trick:
Sub TrimTrailingSpaces()
' usage:
' 1. select the cells where the traling spaces should be removed
' 2. run this macro
Dim rngCel As Range
For Each rngCel In Selection
rngCel.Value = RTrim(rngCel.Value)
Next
End Sub
Filed under: ASAP Utilities, General on September 18th, 2008 by Bastien | No Comments »
Filed under: ASAP Utilities, General on September 17th, 2008 by 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 the same length (123450000 or 123456789) since there weren’t the optional 4 digits on all of the entries.
At this moment I don't see a big general purpose for such a tool but I have added it to the wishlist. If you are interested in such an addition to ASAP Utilities, please leave a comment on this post.
Meanwhile, you can achieve this result in just plain Excel by using a combination of the =LEN() and =REPT() functions.
For example the following formula will fill up the value in A2 up with ending zeros to a total length of 4:
=A2&REPT("0",4-LEN(A2))
As you see in the example the numbers are left-aligned. This is because Excel treats them as 'text' values. If you need to do some calculations on the new numbers with trailing zeros, you can wrap the =VALUE() function around it, which converts a text string that represents a number to a number.
=VALUE(A2&REPT("0",4-LEN(A2)))
Filed under: ASAP Utilities, General on September 17th, 2008 by Bastien | 1 Comment »
