Jul 31, 2015

Spreadsheets are fantastic not just for number crunching but for extracting useful information from large quantities of data. Filtering, sorting, pivot tables all allow for quick and easy summaries, unless the data looks like this:

Blank cells, great for readability and absolutely horrible for anything else. The problem is before we can filter, sort or pivot this data we have to somehow replicate the values in the blank cells but manually copying each value can be extremely time consuming. The good news is that it can easily be done using a few lines of VBA, and the better news is it can be done quickly in a few simple steps without using VBA. Here are both solutions:

1. Click in the first blank cell, in the example above that would be A3.
2. Type “=” and then click the cell above. Press Ctrl+Enter.

3. Press Ctrl+C to copy the formula.
4. Press F5 and then click Special (or on the home tab select the find button on the far right of the ribbon and choose Go To Special.)
5. In the Go To Special dialog box select Blanks and click OK.

All the blank cells will now be highlighted:

1. Press Ctrl+V to paste the formula copied previously into all the blank cells, and voila, all blank cells filled!

2. Finally to replace the formulas with values, click on the first cell in the column and press Ctrl+Shift+? to select all the data. Press Ctrl+C to copy and then Right click and under Paste Options select Paste Values.

The data can now easily be filtered, sorted or pivoted.

While the method above is quite quick and negates the use of macros, if data has to be corrected on a very regular basis this may still be a bit laborious, so here is the macro alternative:

1. In excel press Alt+F11 to open up the VBA editor.
2. Go to the Insert menu and select Module.

3. Copy and paste the following code into the code window:

Sub FillBlanks() Dim intRows As Integer Dim intC As Integer Dim intCol As Integer Dim intColCount As Integer On Error GoTo HANDLER Range(“A1”).Select intRows = ActiveCell.CurrentRegion.Rows.Count intCol = ActiveCell.CurrentRegion.Columns.Count For intC = 3 To intRows For intColCount = 1 To intCol If Cells(intC, intColCount) = “” Then Cells(intC, intColCount) = Cells(intC – 1, _ intColCount) End If Next Next Exit Sub HANDLER: MsgBox “An error occurred filling the blanks” End Sub

To run the macro from Excel, go to the View tab of the ribbon and on the far right and select Macros. The macro dialog will appear, ensure FillBlanks is selected and click Run.