Archive for category Excel

Today, a coworker asked me how to hide a row on his Excel spreadsheet.  This was a fairly large spreadsheet, with about 150 rows and 30 columns.  After showing him, he started to hide every other row, which would be an incredible waste of time for someone in his salary bracket.  So I offered him a solution: a macro that hides (and unhides) the rows by pushing a button.  Really two buttons.  Here’s how I did it:

We’ll start with a simple spreadsheet.  You can follow along with this one: Hide_Row_Excel_File_No_Macros.xls

If you’d rather view a completed file with the macros already present, click this one: Hide_Row_Excel_File.xls

Here’s what the raw spreadsheet looks like:

We want to be able to hide and display the blank rows, so we’ll need to write a couple of macros.  First, we’ll put a formula in column D that checks if column C is blank.  The formula =if(C6=”",”HIDE”,”") is entered in cell D6:

Then, we’ll copy the formula from cell D6 down the column through cell D40:

The macro will run on a simple loop, repeating for the necessary number of times.  We’ll assign a number to each row to determine that number.  It just a formula that adds one to the cell’s value above it in the column.  We’ll enter the formula +E5+1 in cell E6:

We’ll copy this formula from cell E6 down through cell E40:

Next, we’ll establish the number of times the macro needs to run.  We’ll use the formula =max(E6:E40) to determine the number of repetitions, and enter it in cell G1. We’ll enter a zero in cell F1; this will be used as a counter:

Now comes the fun part.  We’re going to record a macro!  It’s fairly easy.  We’ll record a few simple keystrokes, and then edit them for our use.  But first, select cells E6 through E8.  Then, from the top menu, click Tools, Macro, and Record New Macro:

We’ll name the macro HIDE_ROW and click OK:

Now, we’ll record keystrokes.  From the top menu, click Format, Row, and Hide:

Next, click the little Stop button that looks like this:

We’ll need to edit the macro that we just recorded, so from the top menu, select Tools, Macro, and Macros:

Then, select the macro named HIDE_ROW, and click the Edit button:

This will display, in Visual Basic,  the code for the macro that we recorded:

We’ll replace the existing code with this.  This will allow the macro to repeat for the appropriate number of times, and check to see if each row should be hidden or not:

When you’ve finished entering the new code, close the Visual Basic editor by clicking the X in the upper right corner.  Next, we’ll want to make it convenient to run the macro.  We’ll do this by creating a button.  From the top menu, click View, Toolbars, and Forms:

This will display the Forms toolbar.  Click on the second box down on the right hand side, which is the icon for the Button:

I placed the button over cells I2 through J3.  A dialog box will pop up that will prompt you to assign a macro to the button.  Select HIDE_ROW and click OK:

You can edit the name of the button by selecting the text and typing over it:

Now that we can hide the blank rows, we want to be able to display them again, or “unhide” them.  Let’s record another macro to do this.  First, select cells D6 through D40.  Remember the steps to record a macro?:

We’ll call this macro UNHIDE_ROWS:

Then, we’ll record the necessary keystrokes.  From the top menu, click Format, Row, and Unhide:

Next, stop the recording by clicking the following button:

We’ll want to create another button for the new macro, so let’s display the Forms toolbar one more time by clicking View, Toolbars, and Forms.  The Forms toolbar looks like this:

Select the Button icon, and place it over cells I6 through J7.  Assign the UNHIDE_ROWS macro to this button:

To finish, edit the text on the button:

Now, just click the buttons to run the macros.  Click the Hide Blank Rows button to hide the rows, and click the Unhide Blank Rows button to display the rows.   If you have any questions at all, please feel free to ask them in the comments.  I’ll get back to you promptly.

If you’ve found this post helpful, why not subscribe?  I’ll be posting more Excel tips in the future.  Subscribe

• Corporate Barbarian Links: Chainsaw Massacre Edition Photo by lancefisher No, that's not me in the picture.  But I did take the chainsaw out this week, and cleaned up the look of the website a bit.  I got rid of the big, ugly ad in the sidebar, and reduced the amount of categories.  Hopefully this will......
• Of Tapout T-Shirts and Other Free Advertising Photo by bdjsb7 My son is a diehard UFC (Ultimate Fighting Championship) fan, and he's actually gotten me hooked.  I mean, who doesn't love seeing two guys beating the hell out of each other?  He wanted to get a Tapout t-shirt, so we headed on over to Dick's to get him sized......
• Excel How-to: Creating a Pivot Table A pivot table is a merely a summary of data in a format that you can manipulate to suit your needs.  Almost any array of data can be used to create a pivot table, given some simple rules: The column labels must be in the first row, and have distinct......

• ATP World Tour Finals: Nikolay Davydenko, Roger Federer, Juan Martin del Potro At the ATP World Finals, Roger Federer is now ranked 1 in the sport for the 5th time. He's one away from tying Pete Sampras' record of 6 number one spots. He won that trophy at the ATP World Tour Finals in London at the O2 Arena on Wednesday, but......
• Top 20 BlackBerry Mobile Apps for Organization and Productivity [/caption] iPhone this, G1 that, where is all of the love for the BlackBerry? Like the forgotten child in the orphanage, it seems no one talks about the still popular RIM smartphone anymore. Well, it’s still out there going strong, and long before the iPhone and G1, BlackBerry had apps......
• How to Clean Up Outlook Contact Phone Numbers using VBA Introduction [/caption] I got a new phone and when synchronised with Microsoft Outlook, the phone numbers don't dial out properly because of spaces & STD bracketing.  It's an LG - the previous Nokia was great, as was it's synchronising software! Solution I used VBA in a workaround via Microsoft Excel! ......