Categories
Mac & OS X Research Software Sports Uncategorized

GoPro GPS Overlay

Yesterday I decided to get an action camera. The decision between a Garmin Virb and the latest GoPro is probably a topic for another article, so let’s just get the decision out of the way; I ended up getting the GoPro Hero3+ Black Edition. In fact, if I’d decided to get the Garmin Virb this article wouldn’t even be necessary. Why? Read on.

Firstly, I’ll say that there are various reasons for getting an action camera, one of which is the need to record my daily ride to work. Nearly every day I encounter situations that I’d like to have proof of, should the need arise. Read: motorists that probably shouldn’t be on the road in the first place.

Background

So what is this article about? I work in I.T. for a living and like looking at GPS data for my proper rides. So, that means you may or may not find the stuff below a wee bit technical. That all depends what you’re into. Anyway, for the GPS part of things I use a Garmin Edge 800. With an action camera, it makes sense that having some speed or other performance data on screen with the video – it’s the inner geek in me controlling that, no doubt. The Elite version of the Garmin Virb has built-in GPS which makes it very easy to overlay GPS data on top of videos. However, because I bought a GoPro, this is an option I don’t have. Not out of the box, at least. But surely it can be done, right?

The short answer: yes!

Desired result

When I look at a cycling video I’ve made, I want to see the current speed & GPS track at the same time. Not in separate windows, either – on top of the video.

The search

I started out looking online for a way to do what I wanted. I found a few ways, including some software that looked promising. Unfortunately, the software is built for Windows only – I use a Mac. Stumped again!

Fortunately for me I found a blog called Syntax Candy, run by a guy called Bartek – a programmer based in Poland. I found that he’d already written a Java application to do exactly what I wanted – score! There are other apps that will do the same thing, but they aren’t free.

Now, I’ve done a fair amount of development in the past but never in Java. That meant my system wasn’t setup for Java development. The next step? Give my system the ability to compile and run Java applications.

The process

Here’s what I had to do get my system ready to run Bartek’s application. I can only cover what I did on OS X, sorry – YMMV for Windows. Note that ~ refers to your home directory.

  1. Download and install the Java Development Kit. As I write this, version 8 is the current version – click here to download.

  2. Set the JAVA_HOME variable. This step is dependant on your shell – the default on OS X is bash, although I use Zsh. For bash, edit ~/.bashrc and for Zsh edit ~/.zshrc (create the relevant file if it doesn’t exist). Put this line in the file:

    export JAVA_HOME=$(/usr/libexec/java_home)
  3. Download Apache Maven – it is required when running Bartek’s application. Click here to download.

  4. You can put Apache Maven anywhere, but put it somewhere that makes sense as you’ll need to reference that location later. On my system, the full path to Apache Maven is in ~/Applications/apache-maven–3.2.1.

  5. Get the full path to your Apache Maven location. To do this, open a Terminal window and type the following commands. They will return the full path, substituting ~ for the actual path to your home directory. Note that this example assumes your Apache Path is ~/Applications/apache-maven–3.2.1.

    cd ~/Applications/apache-maven-3.2.1
    pwd
  6. Copy the path that is returned – mine is /Users/chris/Applications/apache-maven–3.2.1.

  7. Add the path from step 6 to your PATH environment variable. This isn’t strictly required, but it means you can run Apache Maven without entering the full path to the binary. For bash, edit ~/.bashrc, for Zsh edit ~/.zshrc. On my system, the PATH declaration is as follows – yours will be different, but you can see where I’ve added the Apache Maven path.

    export PATH="/Users/chris/Applications/apache-maven-3.2.1/bin:/Applications/MAMP/bin/php/php5.4.25/bin:/usr/local/bin:/usr/bin:/bin:/usr/sbin:/sbin:/usr/local/MacGPG2/bin"
  8. Download the GpsVideo application from Bartek’s blog. Click here to read and download.

  9. Extract the application’s archive somewhere. It doesn’t matter where, as long as you can access it via the Terminal.

  10. Open a Terminal window and change to the directory you extracted the application to. Type the following command. This might take a while as Apache Maven needs to download a bunch of files that are required to run the application.

    mvn package
  11. In the same Terminal window, type the following command. You must be in the GpsVideo application’s directory for this to work.
    mvn compile exec:exec

Here is what the contents of the Terminal window should look like (yours may look different depending on themes, etc).

GpsVideo - Terminal Output
GpsVideo – Terminal Output

And here is what the app should look like, when running correctly.

GpsVideo - Application
GpsVideo – Application

That’s it! If you’ve done everything correctly, the GpsVideo application should run after a few seconds.

GPX files

The Garmin Edge cycle computers save GPS and tracking data in FIT format (by default). For the GpsVideo application, the data needs to be in GPX format. This means taking the FIT file and converting it using something like Garmin Connect or rubiTrack. rubiTrack isn’t free, but the trial version’s only limitation is the number of activities you can have. Imported activites can be exported as GPX from there or Garmin Connect very easily.

What does it look like?

Here’s a screenshot of what a video produced by GpsVideo looks like – not bad at all!

GpsVideo - Video Sample
GpsVideo – Video Sample
Categories
Archived Software Uncategorized

Set network interface IP address with Powershell

The Problem

While setting up our partner technology centre recently, I found myself switching back and forth between networks so often that I was constantly having to change my laptop’s IP address. For reasons that are outside the scope of this article I’m unable to use the option for an alternate network configuration.

Powershell to the rescue

The solution? Two small Powershell scripts – one to setup my network connection for our corporate LAN, the other to setup my network connection for the PTC. The scripts are shown below – feel free to use them in any way you like.

PTC configuration script {#ptcconfigurationscript}

 $index = (gwmi Win32_NetworkAdapter | where {$_.netconnectionid -eq “Local Area Connection”}).InterfaceIndex $NetInterface = Get-WmiObject Win32_NetworkAdapterConfiguration | where {$_.InterfaceIndex -eq $index} $NetInterface.EnableStatic(“10.0.0.10”, “255.255.255.0”) $NetInterface.SetDynamicDNSRegistration(“FALSE”) 

Corporate LAN configuration script

This script just resets the network adapter back to DHCP

 $index = (gwmi Win32_NetworkAdapter | where {$_.netconnectionid -eq “Local Area Connection”}).InterfaceIndex $NetInterface = Get-WmiObject Win32_NetworkAdapterConfiguration | where {$_.InterfaceIndex -eq $index} $NetInterface.EnableDHCP() $NetInterface.SetDynamicDNSRegistration(“TRUE”) 

Extra stuff

Although I don’t need them in my PTC environment (it’s internal only, with no internet access), you can also use the snippets below to add some extra functionality. Set gateway:

 $NetInterface.SetGateways($gateway) 

Set DNS server search order:

 $NetInterface.SetDNSServerSearchOrder($dns) # (e.g. “10.10.1.1” for single server or “10.10.1.1,10.10.1.2” for multiple servers) 

Enable dynamic DNS registration (e.g. in AD environment):

 $NetInterface.SetDynamicDNSRegistration($registerDns) 

Categories
Archived Software Uncategorized

SQL Server 2008 Express R2 Unattended Install

Back in March 2009 I wrote an article called "SQL Server 2008 Express Unattended Install"  It turned out to be one of the most popular articles on Digital Formula (and still is) so, since SQL Server 2008 R2 Express has been released, I figured I’d write an article refresh on how to do the same thing as the original article, but with the latest version.

To make the change easy, I’ll explain the main things that have changed between 2008 and 2008 R2 from an unattended install perspective.

Assumptions

  • You’ve already downloaded SQL Server 2008 R2 Express. This article will assume you’re installing the 32-bit (x86) version although I see no reason why the steps below won’t work with the 64-bit (x64) version.
  • Your system meets the requirements for installing SQL Server 2008 R2 Express.  Note that this includes installing the .NET Framework 3.5 SP1, Windows Installer 4.5 and Windows Powershell.
  • You are installing from C:InstallSQL2008 (you can change this as necessary but please make sure you update the paths in the examples below).
  • You’ll save your configuration file as C:Installsql-2008-express.ini

Extracting installation files

To get the extracted installation files, run the following command and follow the wizard – easy.  Note that I’ve extracted mine to C:InstallSQL2008Extracted.

C:InstallSQL2008SQLEXPRWT_x86_ENU.exe /x

Getting the default configuration file

This is the key difference between the previous version (2008) and this one (2008 R2).  Run the following command either from the command prompt or from the Start » Run dialog and then follow the steps below carefully – you’ll see that we don’t actually want to complete the install right now.

C:InstallSQL2008SQLEXPRWT_x86_ENU.exe /UIMODE=Normal /ACTION=INSTALL

This will start the installation with all parts of the installation visible.  If you just ran the executable, you wouldn’t see the screen that shows where the default configuration file is.

From here, follow the wizard, do a new install and configure the options as per your requirements, making sure that the steps on the left include the item "Ready to Install".  Note that this will only present if you ran the command above to set the UIMODE and ACTION parameters.

Ready to install option available
Ready to install option available
Categories
Archived Mac & OS X Software Uncategorized

TotalFinder for OS X – Review

Today’s article is something I haven’t done for a while – a review.

If you’re someone that follows the not-often-enough-updated content on Digital Formula, you’ll know that I’ve written reviews in the past but haven’t done that many since making the switch from PC to Mac almost 18 months ago … wow has it been that long?

Anyway, the software in question today is called TotalFinder and is one of the excellent packages put together by Antonin Hildebrand of binaryage.

TotalFinder is a plugin for Finder in OS X that adds some functionality to Finder that I think perhaps should be there in the first place.

What does TotalFinder look like?

Once installed, the default settings are immediately visible when you open a Finder window. If you open a Finder window, the first thing you’ll notice is the addition of tabbed browsing. Yes! Why do I like this so much? Quite simply it’s because tabbed browsing is something that couldn’t come soon enough in the web browser world and it absolutely makes sense here, too.

TotalFinder - Default
TotalFinder – Default Settings

The same keyboard shortcuts apply as before but with a couple of additions:

  • Command+W closes the current tab (or window if there’s only one tab in it)
  • Command+T adds a new tab to the current Finder window
  • Command+N opens a new Finder window
  • Command+U toggles dual mode on or off
  • Shift+Command+; toggles ‘Folders On Top’ on and off
  • Shift+Command+. toggles ‘Show System Files’ on and off
  • Shift+Command+P toggles Visor pinning on and off (see below)

Dual Mode

This is another of TotalFinder’s additions that is critical to its success. Pressing Command-U toggles Dual Mode and gives you a view not too dissimilar to most FTP client applications. If you do a lot of file management that involves copying or moving files between two locations, dual mode will save you a ton of time and save you from wasting time arranging multiple Finder windows.

TotalFinder - Dual Mode
TotalFinder – Dual Mode

One thing I’ve noticed is that when you switch dual mode off, there are two tabs left open even if you only had a single tab open before switching dual mode on. I’m assuming this is by design.

Quickly show or hide system files

As a bit of a geek, this is something that I really like. Pressing Shift+Command+. (period/full-stop) will quickly switch between system files being shown or hidden, an invaluable shortcut if you’re working on system files quite often. No need for a screenshot of that, right? 😉

I want Finder like Windows!

Really? Well, TotalFinder can deal with that, too. Pressing Shift+Command+; (semi-colon) will toggle between folders being shown above all files or the default which is to order the Finder contents by name, regardless of whether its a file or folder.

TotalFinder - Like Windows
TotalFinder – Like Windows

The Visor

This is something that I haven’t seen in a Finder plugin before. The Visor is a feature that can be enabled by opening the Finder preferences window, selecting the TotalFinder section and switching on ‘The Visor Feature’. Once on, you can press the default keyboard shortcut (Option+`) to quickly show a screen-width Finder window. It can also be pinned in place so that the Visor doesn’t lose focus when hidden.

If you don’t like the screen-width Finder window you can also use what’s called ‘FreeForm Window’ to set your own configuration for the Visor window.

Do I want TotalFinder?

If you’re reading this website it’s probably a fair bet that that you’d get some decent use out of TotalFinder. Most people that come here are either I.T. types like me or keyboard junkies (also like me). I don’t use the mouse unless I really have to and reckon TotalFinder contributes to my shiny Microsoft mouse gathering dust – for me that’s a good thing.

Now, to be clear, TotalFinder isn’t free. It costs $15 for a single license or the equivalent of $10 per license if you buy the three license pack for use on three computers. It’s worth mentioning that Antonin, the developer of TotalFinder, does offer an interesting licensing concept where you can get a free license if you meet certain criteria.

Go on, try it. You know you want to!

P.S. Yes, I’m running the trial version of TotalFinder and there is a TimeMachine Backup running as I write this! 😉

Categories
Archived Mac & OS X Software Uncategorized

Running HandBrakeCLI from Hazel

Recently I’ve been working on some video I shot back in 2009 while in Whistler, Canada on a mountain biking trip.  I’ve been exporting them as fairly standard .AVI files because that produces good results but I’ve recently started adding them into my iTunes library so I can watch them on my Apple TV.  I use HandBrake to convert them to .M4V files which can then be imported into iTunes.  This is a manual process, though, so I set about trying to automate it without having to get my video editing program to export to .M4V (the results weren’t very good for some reason).

Hazel

Using Hazel from NoodleSoft, I’ve setup some rules so that when a file of a particular type appears in a selected folder, it runs a Bash script to convert the video and put it into ~/Movies/iTunes.  Hazel isn’t free but is worth every cent of the $21.95 USD that I paid for it.  Anyway, if you’ve got Hazel you’ll probably know how to add a folder rule so I won’t go into that here, although the screenshot below should be all you need.  Here’s the script I run from Hazel.

FILEIN=$1
FILEOUT=`echo "$FILEIN" | awk -F. '{ for (i=1;i<NF;i++) printf $i"."; printf "m4v"}'`
HandBrakeCLI --preset "AppleTV 2" --input "$FILEIN" --output "$FILEOUT"
mv "$FILEOUT" ~/Movies/iTunes/
mv "$FILEIN" ~/.Trash/

Important note: I’ve just noticed that the Syntax Highlighter plugin kinda breaks the code if it has certain characters in it.  If you’re going to copy the script above, change &lt; to the < symbol.

And here’s a screenshot of how my Hazel rule’s configuration looks.

Hazel rule
Hazel rule to run HandBrakeCLI

That script will set a variable called FILEIN to the name of the file being processed, set a variable called FILEOUT to the same filename but with the extension changed to "m4v" without the extension then, using those variables, run HandBrakeCLI to convert the video using the built-in preset called "AppleTV 2".  The processed video is then moved to the Movies/iTunes folder in my home directory and the source file is moved to the trash.  Note that the file isn’t deleted per se, just moved to the trash.  I might want it back later, who knows …

HandBrakeCLI

The second part, of course, is that you need HandBrakeCLI before the script above will work.  I’ve got HandBrakeCLI in ~/_Applications but you can put it anywhere.  Don’t forget to specify the full path to HandBrakeCLI unless you add the directory name to your PATH environment variable.  HandBrakeCLI is in my PATH so I haven’t specified it above.

The Results

So what happens?  Once a file had finished exporting into the appropriate folder, Hazel realises it’s there and runs the script that calls HandBrakeCLI.  The command line options specify that the video should be output in a format that’s compatible with Apple TV, and therefore iTunes.  I could use the iTunes "Automatlcally Add to iTunes" folder but I’ve decided not to at this point.

I’ve got this setup on my Mac Mini server and it just sits there, patiently waiting for the .AVI files to appear in the appropriate folder.  Easy.

Categories
Archived Mac & OS X Software Uncategorized

iTunes Media Management Made Easy

I use iTunes to manage all my media now.  Since I don’t use Windows anywhere at home I made the decision to add everything to iTunes, including music, movies and TV shows.  One of the problems I ran into, though, was after ripping one of my TV show collections from DVD I found that I’d messed something up and ended up with a ton of episodes with the right show name but the wrong episode numbers.  Painful!

I did some searching and came up with Doug’s AppleScripts for iTunes, a collection of AppleScripts that includes a ton written specifically for managing iTunes.

Doug has written one called Set Video Kind Of Selected, currently version 3.2 as of February 5th 2011.  It allows you to make a selection and set the starting episode number (amongst other things) so that all the selected media is sequentially numbered from the number you entered.  This is such a simple idea and yet SO useful – it totally solved my media management problem in this case.

When you run it, you’re presented with a small dialog box that allows you to set video kind, show name, season number and episode number start for the selected tracks.  Your changes, once made, are applied when you click the "Done" button.

iTunes Script - Set Video Kind of Selected
Set Video Kind of Selected

Unfortunately for those readers using Windows, this one ain’t for you – AppleScript only works on OS X.  Sorry.

All my TV show issues are now solved and the episodes numbered correctly.  Cool.  🙂

Categories
Archived Software Uncategorized

Microsoft Excel – Delete duplicate rows

I’ve been meaning to put this online for some time but kept forgetting to get it done.  This article is simple and provides a small Excel VBA script that makes it easy to delete duplicate rows from an Excel worksheet.

All you need to do is copy the code into an Excel module or class, depending on how your Excel application works.  Then you just provide the relevant row ranges and the script will do the rest.

Sub DeleteDuplicateRows()
    ' Chris Rasmussen January 2011
    ' DeleteDuplicates
    '   goes through the range specified below and, if a duplicate value is found anywhere in the range, deletes that value's entire row
    '
    Dim x As Long
    Dim LastRow As Long
    Dim duplicateCount As Integer

    duplicateCount = 0

    LastRow = Range("A300").End(xlUp).Row
    For x = LastRow To 1 Step -1
        If Application.WorksheetFunction.CountIf(Range("A1:A" &amp; x), Range("A" &amp; x).Text) &gt; 1 Then
            Range("A" &amp; x).EntireRow.Delete
            duplicateCount = duplicateCount + 1
        End If
    Next x
    If duplicateCount &gt; 0 Then
        Results = MsgBox(duplicateCount &amp; " duplicate rows removed.", vbInformation, "Duplicate rows")
    Else
        Results = MsgBox("No duplicate rowsfound.", vbInformation, "Duplicate rows")
    End If
End Sub

Easy.  🙂

Categories
Archived Software Uncategorized

Easy schedules for HP Data Protector

On a number of sites now, including 5 main current ones, I’ve used HP Data Protector, one of HP’s enterprise-level backup software packages … depending on who you talk to.

Anyway, because Data Protector, originally called HP OmniBack, was ported over from the UNIX platform it means that most of the configuration can be done by editing plain text files.  This includes the schedules, although the schedule text files can often get pretty messy once you’ve made a few changes.  Below are a few backup schedules that have had all the fat trimmed out and don’t have anything you don’t need in them.

Daily & weekly full at 1830

This job runs a full backup every Monday-Friday at 1830 excluding the first Friday of each month.  I usually choose to run a monthly backup on the first Friday of each month so this is a necessity for me.

-full
-every
      -day Mon Tue Wed Thu
      -at 18:30

-full
-day 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
-at 18:30

-full
-exclude
-day Sat Sun
-at 18:30

Daily differential at 1830, weekly full at 1830

This job runs a differential backup (incr1 in DP) every Monday-Thursday at 1830 and a full backup every Friday at 1830, excluding the first Friday of each month.

-incr 1
-every
      -day Mon Tue Wed Thu
      -at 18:30

-full
-day 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
-at 18:30

-full
-exclude
-day Sat Sun
-at 18:30

Monthly full on the first Friday of each month at 1830

This job runs a full backup on the first Friday of every month at 1830.  It fits in with the schedules above.

-full
-every
	-day 1 2 3 4 5 6 7
	-at 18:30

-full
-exclude
	-day Sun Mon Tue Wed Thu Sat
	-at 18:30
Categories
Archived Mac & OS X Software Uncategorized

What tools, software & applications do I use? v3.0

This post is intended as a couple of things. Firstly, it’s the 2nd rewrite of a post I wrote a while ago that covered the same thing as this one covers but from when I was running Windows.

I’ve actually had a couple of people ask me what tools I use for various tasks so I’m going to compile a list of the various applications I use ‘every day’. That’s in quotes as obviously I don’t use all of these tools EVERY day but I consider them part of the list of tools I couldn’t do without. I don’t use anything particularly special but I’ll make this list for those that’ve asked anyway. Secondly, I keep meaning to make a list of tools I use for my own reference so it’ll double as that, too.

I’m not going to go into a whole load of detail about what the benefits of each one are – I’ll let you work that out for yourself if you want to try them (feel free to contact me if you want to discuss any of these though). I will include a couple of things which aren’t strictly applications too, e.g. hosting services. So, here we go – these details are correct as at July 18th, 2010.

Applications & Utilities

  • » Operating system :: Mac OS X 10.6.4 (Snow Leopard) on a 27″ iMac and a 15″ Core i7 Macbook Pro. I made the switch from PC to Mac towards the end of 2009 and am wishing I’d done it years ago (based strictly on what I use my machines for). Please keep comments about Mac vs PC to yourself as I’m not interested in comparing the two – they’re both great for different reasons.
  • » Office Suite :: Microsoft Office 2008 for Mac … unfortunately I have to say that compared to the Window version this really is quite crap. Office 2011 for Mac promises to bridge the significant feature gap between Office for Windows and Office for Mac, though.
  • » Email client :: Mozilla Thunderbird 3
  • » App dev environment :: Xcode 3.2.1 when writing for OS X, Visual Studio Express (C#) when writing for Windows (Parallels Desktop)
  • » Web dev environment :: Coda by Panic
  • » FTP client :: Transmit by Panic
  • » MySQL administrator :: Navicat Lite
  • » Text editor :: Fraise
  • » Primary browser: Mozilla Firefox 3.6
  • » Other browsers: Opera 10.6, Google Chrome, Safari 5
  • » Twitter client :: Kiwi for Mac
  • » Password storage :: KeePassX – *highly* recommended on both OS X and Windows (the Windows version is called KeePass although KeePassX is available for Windows, too).
  • » iPod manager :: iTunes – on OS X it’s actually really good and seems 100x more stable than the Windows version.
  • » Photo processing :: Adobe Photoshop Lightroom 3 – I used to use Capture One 4.8 from Phase One but it started to fall WAY behind Lightroom in terms of post-processing options … a real shame.
  • » Fitness management :: Rubitrack for Mac (my training device is a Garmin 310xt)
  • » Instant messaging :: Windows Live Messenger>
  • » Movie player :: VLC (everyone should use this although it struggles with the TSCC codec unfortunately).
  • » Audio player :: iTunes

System Stuff

  • » System management :: MacKeeper.  The differences this thing can make to your Mac’s performance (which is probably already pretty sweet) are astounding.
  • » System hackery :: Tinker Tool.  Purely for the Mac geeks out there.

One thing you might notice is they’re pretty much all free tools with the exception of Lightroom, Rubitrack, Coda, Transmit and MacKeeper, all of which I own licenses for (no they’re not pirated!).

Online Services

  • » Website host :: ICDSoft – these guys are AMAZING. I wouldn’t use anyone else for Linux hosting.
  • » Email service :: Gmail
  • » Website CMS :: ExpressionEngine 2.1 and WordPress 3.0
  • » Online photo sharing :: Flickr (I own a ‘Pro’ account)
  • » RSS feed management :: FeedBurner
  • » Website statistics :: Google Analytics
  • » Search engine :: Altavista … haha yeah right. 😛
  • » Bookmarks :: Delicious – I stopped using browser-based bookmarks many years ago. Ok, so bookmarks are considered a bit backward now but I’ve got stuff on Delicious that I refer back to all the time. Thankfully the functionality doesn’t seem to have changed at all since its acquisition by Yahoo some time ago.

That’s about it really. As I said earlier I’m happy to discuss any of these if you like – just post a comment or use the Contact Me link at the top of the page.

Thanks!

Categories
Archived Software Uncategorized

Microsoft Excel – Delete matching rows

The problem

As part of my new role at Fujitsu here in Wellington I’ve been running a whole load of reports from the SAN I’ve been assigned to. I’m not going to go into any detail about the SAN itself other than to say it’s from HP (for obvious reasons) but the reports I get from the reporting tools aren’t that useful in themselves. Many tens of thousands of lines of reporting data is hard to read as it is and unfortunately these particular reports aren’t uniformly arranged i.e. there is a header at the top of the file, a set of performance data, a blank line and then another set of headers followed by more performance data. This pattern repeats for the entire file.

Microsoft Excel is ok at importing data from CSV files but can’t handle these files too well as there’s no uniform pattern that it seems to be able to match (it matches to the human eye but I guess not to Excel’s internal algorithms or something).

The solution

Anyway, to import these files into Excel I needed to do 2 things which seem pretty simple but that you really don’t want to do when the files are as big as these ones – 40MB+ of text. Yuck. Please note that the first step below works in both Excel for Windows and Excel for Mac but the second part, because it contains VBA script does not work in Excel for Mac (VBA isn’t supported in Excel 2008 for Mac). See the criticism section of this Wikipedia page for information on that.

Job 1: Delete all the empty rows. Thankfully this one’s easy. Select the entire worksheet or range of cells you’re working with, hit F5, select Special, check the box labelled Blanks and hit OK. This will select all the blank cells in the selection. From here select Edit, Delete and when prompted, select Entire Row. Alt-E then D will work in Excel 2007 as it doesn’t have an Edit menu as such. This will do exactly what it says and delete the entire row containing the selected blank cells. Be careful doing this with non-tabular data.

Job 2: Delete all the headers except the top row. Unfortunately I haven’t made this work without deleting the top row too but if you copy the top headers to a new sheet first you can easily paste them back later. In Excel 2007 hit the Developer tab (it can be enabled in Excel options if it’s not showing), click Visual Basic and add a new Module by right-clicking the top pane on the left. Paste the following code into the module that gets created:

The script

Sub DeleteRows()
    Dim Rg As Range
    On Error Resume Next
    For i = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
        Set Rg = Nothing
        Set Rg = Rows(i).Find("Time")
        If Not Rg Is Nothing Then Rows(i).Delete
    Next
End Sub

Change “Time” to a text pattern that will match the rows you want to delete. In the data I’m working on all the header rows have a “Time” column so this works for me.

From here it’s just a case of hitting the Run button and all the rows containing the word “Time” will be removed. Please note that in Excel 2007 you’ll need to save the file as an Excel Macro-Enabled Workbook once a file contains macros or VBA code.

Handy and much faster than deleting them manually.