A Better Way to Copy Filtered Rows Using VBA

May 23, 2013 1 comment »

One of the more common tasks done with VBA is copying filtered rows into a new sheet.

I noticed that most of the Excel users I encounter do this by recording a macro that captures the following movements:

  • Click the top-left corner of the table
  • Press Ctrl+Shift+Down Arrow
  • Press Ctrl+Shift+Right Arrow
  • Press Ctrl+C to Copy
  • Start a new Workbook and then Paste

.

This usually leaves them with a macro that looks something like code seen below. Note the Selection.End(xlDown) and Selection.End(xlToRight). This is really the hard way to go; plus this could possibly be dangerous if the data in the table is not reliably filled in completely.

.
A much easier and safer way is to use the built-in AutoFilter.Range object of the worksheet.

This code does the same action more cleanly.

ActiveSheet.AutoFilter.Range.Copy

Workbooks.Add.Worksheets(1).Paste

.

Now you know.

.

Like this tip? You'll find lots more of these gems in this awesome book.

Yes…there's a redneck on the front cover, but don't let that deter you. The author is extraordinarily smart and handsome.

Click the book see.

Removing the Background from an Image in Excel

May 21, 2013 2 comments »

Since John Walkenbach shut down his J-Walk blog back in 2011, I've been reading his Google Plus page where I get a daily regimen of the silly, odd, and absurd things he finds on the internet. It's usually good for a few good chuckles. But today I actually learned something new about Excel.

.

John mentioned that Excel can remove the background from an imported image. I didn't know that! I'm guessing most of Excel's non-artistic nerd community missed this too.

Apparently, this feature works in Excel 2010 and Excel 2013 (not sure about 2007).

Here's the deal:

.

Let's say I import a picture like this one into Excel, and I want to crop out everything except the picture of John playing the banjo on the right.

.

I can click on the picture while it's in Excel, and choose the Remove Background command on the far left of the Format tab.

This immediately puts a purple box around my picture.

I start by adjusting the handles to the area that I want to keep. Excel does a pretty good job getting close to removing everything but John. But you can see that it missed his feet, part of his head, and part of the banjo (they're still in purple).

.

So this is where I can use a few commands on the Background Removal tab.

Mark Areas to Keep: Allows me to tag the areas to keep in my picture

Mark Areas to Remove: Allows me to tag the areas to crop out

.

The idea is to select Mark Areas to Keep and draw a line through the areas that I want to keep. Then I select Mark Areas to Remove and draw a line through all the areas that I want removed.

.

Once I'm ready, I can click the Keep Changes command. My reward is this fine picture of Zen Walkenbach playing banjo on one foot.

.

He looked uncomfortable, so I gave him something to sit on.

.

Thanks for the tip John!

Building Waffle Charts in Excel

May 3, 2013 4 comments »

I've been toying around with Excel Waffle charts (sometimes called Square Pie Charts). It's an interesting visualization that I've recently used to display progress toward goal.

.

As you can see, a Waffle chart is basically a square is divided into a 10×10 grid. Each grid box represents 1% toward a goal of 100% percent. The number of grid boxes that are colored or shaded is determined by the associated metric. This kind of chart is a relatively effective option when you want to add an interesting visualization to your dashboard without distorting the data or taking up too much dashboard real estate.


There are several ways to achieve this visualization in Excel. My personal preferred method is to use an actual chart object. Although there are easier ways to implement this type of visualization (with conditional formatting in cells), using an actual chart object allows me to easily resize and move the visualization to fit my dashboard.

.

In this post, I'll walk you through the steps to set up a waffle chart template and how to duplicate it for as many metrics as you need.

. » Read more: Building Waffle Charts in Excel

Access 2013 Bible in Stores on Monday

April 26, 2013 9 comments »

I've been suffering a flu that one of my kids infected me with; no doubt passed to me by coughing into my mouth. I've come to the conclusion that children are walking petri dishes.

.

Anyway – I'm back from the brink of death and I want to share a couple of things before heading into the weekend.

.

Another Live Excel Training Event in October
The Dallas Power Analyst Boot Camp sold out in record time. All seats have been filled ; thanks to everyone who signed up.

Because this event was filled up so fast, I'm considering adding another event in October. Maybe I can get one of the other MVPs to do it with me.

What city should I go to? (Atlanta, Miami, Charlotte, Phoenix, Los Angeles, Washington DC, Baltimore, any others)?

.

The Access 2013 Bible Hits Stores and Amazon on Monday!

Dick Kusleika (of Daily Dose of Excel fame) wrote the Access 2013 Bible with me. I'd like to say we knocked this out over a couple of beers like men. Sadly, the truth is uglier. We were both alone in our own houses (probably at 2am in black socks and white boxers) feverishly trying to get a few pages at a time done before stumbling to our day jobs. DK and I tackled this book over the course of about 5 months.

.

I know I'm biased, but I truly think that this version of the Access Bible is the best in the series. We reorganized the chapters, we added a bunch of new content, and Wiley added a new (very efficient) layout.

.

Consider picking it up if you want to advance your skill-set to encompass database design, application development, or integrating Excel and Access.

This book is fun for the whole family.

.

I'll be back on Monday.

Split Data into Several Columns Based on Carriage Returns

April 10, 2013 2 comments »

In my life changing post yesterday, I showed Allen how to find and replace carriage returns (or Alt+Enter).

In that post, I explained that Ctrl+J is the hot-key representation of carriage returns. So you can find and replace carriage returns by entering Ctrl+J in the Find What field.

.

This got me to thinking; could we use Ctrl+J in the Text to Columns dialog box to split data into several columns based on carriage returns? It turns out we can.

. » Read more: Split Data into Several Columns Based on Carriage Returns

Find and Replace Carriage Returns

April 9, 2013 5 comments »

Allen writes and asks this question:

"DataPig, is there any way to find and replace Alt+Enter without resorting to VBA?"

Good question Allen. The answer is yes; here's how.

.

Apparently Allen has a spreadsheet where the creator entered data, pressed Alt+Enter to force a carriage return, and then entered more data. Like this for example:

» Read more: Find and Replace Carriage Returns

Friday Brain Candy

April 5, 2013 2 comments »

It's Friday. Here are a few pieces of brain candy to help you wind down the week.

.

Google Glass

The buzz around Google Glass is hitting a fever pitch. Apparently, this is contraption will start a wave of new wearable devices that will keep us all hooked into the internet. I'm sorry, but » Read more: Friday Brain Candy

Bill Jelen demos Excel GeoFlow

April 2, 2013 2 comments »

It looks like Microsoft is about to release another excellent BI Add-in pretty soon.  This new Add-in is called GeoFlow.   GeoFlow is a mapping tool that enables highly interactive location intelligence based on data you feed to it.

.

It's not released to the public yet, so I haven't had a chance to put it through its paces.  But Mr. Excel (Bill Jelen) created this nifty demo that shows some features of this new tool.
» Read more: Bill Jelen demos Excel GeoFlow

Use Data Explorer to Hack into Very Hidden Sheets

March 29, 2013 7 comments »

This weekend is Easter; a time when ungrateful children run around like weasels trying to find hidden eggs. In the spirit of finding hidden objects, I'll show you a way to find hidden data.

.

While playing with Data Explorer, I noticed that could see data I shouldn't be able to see; hidden sheets in Excel workbooks for example.

Here's what I mean.
» Read more: Use Data Explorer to Hack into Very Hidden Sheets

An Excel Prank for April Fools

March 27, 2013 11 comments »

Monday is April Fool's day; a special time where tom-foolery and ballyhoo are encouraged.

It's time to prank your co-workers into feeling they have dementia.

.

A time honored tradition among Excel nerds is to mess with a co-worker's Excel Option settings to cause confusion and hilarity. Some of the old ones are:
» Read more: An Excel Prank for April Fools