Spy Journal 3.0 Filtering the web for you

A while ago a reader named Jeremy sent me a request. Sorry I took so long to respond Jeremy – but I’ve finally done this and here are the results.

How can I delete an entire row based on the font color in column A?

I want do delete every row between A2 and A1000 where the font color is black or automatic. I have tried various alterations of the codes below.

Jeremy supplied several procedures, none of which worked correctly.

I have built a very simple version that does what is needed. I know that this is not good code, but it provides a good starting point. And it works. Anybody who needs to use the guts of it (the selection and deletion based on the font colour) can take that and apply it to whatever ranges they need to.

Here is the scenario.

1. You have a range of cells that have been coloured with the automatic font colour. (this could be any colour – you would just need to change the colour index in the code.)
2. You have some items that have been coloured with a different colour.
3. You need to delete the entire row of any cell that has not been coloured specially, ie all the automatic colour cells.

As Office 2010 becomes more prevalent in the workplace (most of our clients either have or will be soon updating to it) so the use of the ribbon has become more natural and intuitive for people. It is certainly much more intuitive than the old menu driven interface.

Play a fun game called Ribbon Hero 2 that allows you to learn the fastest and best ways to do various common tasks in the various Office Apps. My kids are using this to learn the interface and some of these common tasks. I have even learnt some things in the 5 minutes I have spent playing it so far! Download Ribbon Hero 2.

Take the first step in growing your Excel Skills. Microsoft have created a series of learning videos that assist new users to Excel to grow their skills

Learn how to create your own Excel Macros with a free training course. Ideal for the entry level person who wants to explore macro writing for the first time. Note this doesn't create efficient code, but its a start to understanding how the VBA code interacts with Excel.

PowerPivot for Excel - if you have looked or used PowerPivot (See my review of Power Pivot) than this page has some good links for additional resources.

There is just so much going on with the whole change over from Office 2007 to 2010, and for those of you still stuck in 2003, even more important for you to start convincing your organisations of the need to go straight to 2010.

We have been running the beta 2010 here solidly an all our main machines here for 2 months now, with some individual test machines previous to that. Unfortunately now we have to have test and development environments in both 2003 and 2007 so that has doubled our required number of Virtual Machines.

Following are some of the latest news and bits and pieces as well as some other MS Office related information I have come across recently.

Excel 2007 and 2010

Conditional formatting. Conditional formatting in 2007 is one of the main reasons people come to Spyjournal.biz. These two articles are the single most hit articles of the thousands here with over 50,000 views between them:

• Conditional formatting in Excel 2007 - entire row colours
• Excel 2007 Conditional Formatting

Amit Velingkar has written conditional formatting rules simplified while Dick Kusleika has written some code to list out all the conditional formatting rules in a worksheet

I came across an interesting issue yesterday. I found a limit that I was previously unaware of in Excel 2003.

I had written some code that was taking data from a large data set into an array, filtering for some specific information and writing the results to another array. Then I was attempting to paste that array data into a range in the spreadsheet as a report. All was working well until I selected one specific set of inputs and ran the report. Suddenly it stopped pasting halfway though the paste. That is it would paste some of the data and not the rest and the code would break on the paste function. After some analysis I determined that the particular cell it kept breaking on had 1400 characters in it. Some research on Google helped. I discovered two things.