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

Remove comment spam from your WordPress database

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

Remove all rules in Outlook at once

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 the […]

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

Excel options

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

Help, my columns are displayed backwards

View current sheet right-to-left: Off

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

Tip: Insert multiple rows at once

tip_insert_multiple_rows_by_hand.gif

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

Color a cell by just clicking on it

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

Screenshots of the different Excel versions

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

Excel startup errors and speed problems

There are several things that can affect the speed of Excel or cause startup problems. I've found five good sites that discuss these problems and offer solutions: Chip Pearson: Startup Errors In Excel Microsoft: How to troubleshoot startup problems in Excel David McRitchie: Slow Response, Memory Problems, and Speeding up Excel JKP: Fixing Startup problems […]

Round up formulas with =ROUND()

I just got a call from a friend of mine. He needed to edit a spreadsheet and round a bunch of formulas. Since the rounding is important for the calculations, a simple cell-formatting wouldn't do. Excel's worksheet function =ROUND() can be used to round the numbers. However editing a lot of cells by hand (especially […]

So much spam these days

It has been on of those 'spam-periods' again. My inbox this morning: Fortunately most spam is detected and all virussus are removed, but still a lot of rubbish comes through the filters. I use Outlook 2003 and I have also tried Thunderbird (Mozilla) but still too much spam comes through in both mail clients. My […]

Conditional row color based on a cell value

Jeremy (US) asked me a question about coloring based on a cells value: First your product ASAP Utilities is great and has been a life saver. One feature that would be outstanding is a conditional row color based on a cell value. For instance there are numerous times I have to color a row because […]

Create a colorindex table

I'm working on an ASAP Utilities User Guide. When describing some of the worksheet function that use the colorindex property, I needed a good example of all available colors. So I wrote a quick macro to create a colorindex-table but enhanced it to show the colors in the same order as it is shown in […]

Measurement system in Excel to metric

Yesterday I got a question about the unit system in Excel. Andy from down under wrote: I am not a huge user of all the complex stuff that excel does but your ASAP has made my work easier. Simple thing like changing case when I've left the caps lock on!! I do have a question […]

Sort by color with ASAP Utilities

(Update October 18, 2007: If you want an easier way to do this, see this post: Video: How to sort by color in Excel with ASAP Utilities) In Excel you can only sort by value, not by formatting such as cell colors. With ASAP Utilities you can! Here's a short example how to do sort […]

Welcome

Hello world! This blog will be use to post frequently about Excel tools and provide code snipplets. As the author of ASAP Utilities I get a lot of questions by email. Most questions are the same and repeated over and over again. I'll use this blog to answer to the most general Excel questions including […]

Extract hyperlinks

A few weeks ago I got a question about hyperlinks. A list of soccer clubs was kept in Excel (4000+). Some of them had a hyperlink some didn't. How to see this quickly? A possible solution is to show them in different colors: Private Sub ColorHyperlinks()  ' color cells with hyperlinks  Dim rngCel                                 As Range  Application.ScreenUpdating = False  For […]

Sum by cell color

Last week I got a question from Mike: Bastien, Thank you for writing ASAP Utilities. It has saved me countless hours in Excel time and has given me capabilities that I wouldn't otherwise have. I wanted to ask for some help. I noticed that in the user-defined functions for ASAP Utilities there is a function […]

Page 2 of 212