Last week, Seth Godin recommended hiring a geek to help you save an hour a day.
Well, you’re a geek, so you don’t have to hire one! Just pay attention as you run through your morning office routine, and answer this question:
What can you change about your Excel habits, to save an hour a day, or even a few minutes?
Then, make the change.
Doing Steps Manually
Every morning, I follow these steps:
download some website statistics to Excel,
crunch the numbers, and
save the raw data file.
Fortunately, I have a macro that does the first 2 steps. For some reason, I was doing the last step manually. Don’t ask me why.
Excel is a bit slow when opening the Save As dialog box, so that final step was taking 30-60 seconds. Not a huge productivity drain, but why do something manually, if it can be easily automated?
Make It Automated
To get rid of that manual step, I added a few lines of code to the existing macro. The Excel VBA code saves a copy of the active workbook, into the Backup folder, and adds the previous day’s date to the file name.
It only took me a couple of minutes to add the code, so the time invested was quickly repaid. Now I just have another 59 minutes to trim!
Macro Code to Save Daily File
'------
Sub SaveDailyData()
Dim wbData As Workbook
Dim strDir As String
Dim strName As String
Dim strExt As String
Set wbData = ActiveWorkbook
strDir = "C:\Backups"
strName = "DailyData_" & Format(Date - 1, "yyyymmdd")
strExt = ".xls"
wbData.SaveCopyAs strDir & strName & strExt
wbData.Close SaveChanges:=False
End Sub
Most of the time when you’re sorting in Excel, you sort based on the values in one or more columns.
If your workbooks are like mine, it’s rare that you sort horizontally, based on the values in a row. But if you need to do that, here’s how to sort a row in Excel.
I’ve done another update to the Data Validation Multiple Selection sample, thanks to a question from Pat, in a blog comment. He has 3 columns with different drop down lists, and wants to add new items to the applicable lists.
I’m working on some pricing reports for a client, and one of the requests was for a Pocket Price Waterfall chart. I hadn’t made one of those before, and fortunately the client sent me a sketch of the chart that they wanted.
The file is in Excel 2007 format, and is zipped. It contains macros, so enable them if you want to test the code.
Related Excel VBA Tutorials
If you’re looking for a fun and exciting way to fill your holiday Monday, here are links to a few other UserForm and Excel VBA tutorials on my Contextures website:
If you have been using the Ribbon in Excel 2007 or Excel 2010 for a while, you can probably find most of the commands that you need. It’s those infrequently used commands that cause the problems. You knew where they were on the old menu bar, but where the heck are they on the Ribbon?
[Update: This Microsoft download is no longer available]
The Office Labs people at Microsoft have taken pity on our poor, fumbling fingers, and come up with a solution – the Search Commands add-in. This free tool lets you type the name of a command, and magically find it. Think of all the time that you’ll save!
For now, at least, the add-in is only available in English, and works in Word, Excel and PowerPoint. It requires Windows XP, or later, operating system.
Download the Search Commands Add-In
To install the Search Commands add-in, go to the download page on the Microsoft site.
Optional – Click on the plus signs, to see
Details
System Requirements
Install Instructions
Then, click on the Download button, to get the file. Next, run the downloaded file, to install the add-in.
Search for Commands
After you run the setup program, to install the add-in, a Search Commands tab automatically appears on the Ribbon.
In the Search box, at the left end of the Ribbon, type the name of the command that you want to use. For example, if you can’t find the command to protect the worksheet, type “Protect” or “Protect Sheet”.
Click the magnifying glass symbol, to start the search
One or more commands that match your search term will appear in the search results section, to the right of the search box.
If there are several commands that match your search words, you might need to use the Previous and Next buttons to scroll through the list of commands.
For example, if you type “Protect”, instead of “Protect Sheet”, there are 16 commands found, shown on 2 pages.
Use the Search Results
If the search results include the command that you were looking for, just click on it, to run that command. Quick and easy!
And to find out where that command lives on the Ribbon, point to the command, before you click it. The popup description shows the location of the command, by listing the Tab, command group, and command name.
It’s funny – I didn’t realize that the Protect Sheet command was also on the Home tab. I expected it to point to the command on the Review tab. It must look for the first instance of the command in the Ribbon tabs.
Also, the location description doesn’t mention that you have to click the Format command, to see the Protect Sheet command, so you might need to do a bit of searching, to find the exact location.
Commands Not in the Ribbon
Another nice feature of the Search Commands add-in is that it even finds some commands that aren’t in the Ribbon.
For example, if you type Pivot, it shows the PivotTable and PivotChart Wizard command.
Room for Improvement
The Search Commands add-in is a work in progress, so it’s not perfect yet.
It should have full descriptions of the location, as mentioned in the Protect Sheet example above.
The Search Commands tab doesn’t shrink, when you make the Excel window narrower, like other tabs do.
Include settings that are in the Excel Options window, like hiding zeros on the worksheet, or changing to R1C1 Reference Style.
What features would you like to see in the add-in?
Customize Excel Ribbon Tab
You can also customize the Excel Ribbon, by moving or removing the groups on a default Ribbon tab. In this video, see how to move groups on the Data tab, so the tools that are used most often are in the middle of the tab, and easier to reach.
When you’re working with Excel VBA, you might want find the last row with data, so you can paste new data in the row below that.
The following code works up from the last row on the worksheet, until it hits a cell with data. It’s like using the End key and Up arrow, to manually move from the bottom of the worksheet.
Sub GetLastRow()
Dim ws As Worksheet
Dim lRow As Long
Set ws = ActiveSheet
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
MsgBox lRow
End Sub
In the sample sheet, the last data in column A is in row 10, and that’s the result when running the code.
Unexpected Result
This week, I was using similar code in a client’s workbook, to find the last row of data. The data was in a named Excel table, and the last couple of rows in the table were blank. I wanted to find the last row with data, so I could change the table definition, to end at that last row.
When I used the same Excel VBA code, the last row wasn’t what I expected – it found the last row in the named table, even though that row was empty.
I’m sure that information is helpful in some situations, but it sure wasn’t going to help me resize the table!
My Last Row Workaround
There might be a more sophisticated solution to this problem, but I added a line of code to resize the table, so it ends at row 2.
Then, the code found the correct last row of data, and resize the table to end at that row.
Sub ResizeTheTable()
Dim ws As Worksheet
Dim lRow As Long
Set ws = ActiveSheet
ws.ListObjects(1).Resize ws.Range("$A$1:$H$2")
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
ws.ListObjects(1).Resize ws.Range("$A$1:$H$" & lRow)
MsgBox lRow
End Sub
That fixed the problem, and the table resized correctly.
A Better Solution?
Have you run into this problem with named Excel tables? How did you solve it?
Video: Create an Excel Named Table
When you work with lists in Excel, use the built-in Table feature, to make it easy to sort and filter your data. This short video shows the simple steps
