Last updated: 30/09/2017, 1 min read
Introduction
Have you ever had a worksheet without all the labels filled in? The results of a pivot table maybe? You might think that the data in the left table looks tidy and are well-understood. Although this might be true, an arrangement like this causes problems if you need to further work with the data (especially if you need to sort them) due to the blank cells. By using the macro below, in a few seconds, the empty cells will be filled with the appropriate values, and you will have a table looks like the right one.
VBA code
The short VBA code used for filling the blank values is the following:
Option Explicit
Sub FillBlankTableCells()
'--------------------------------------------------
'Fills all the blank cells of a table.
'Written Βy: Christos Samaras
'Date: 22/07/2011
'E-mail: [email protected]
'Site: https://myengineeringworld.net/////
'--------------------------------------------------
'The error structure is used in the case that there is no blank cell.
On Error Resume Next
'Current Region is used because we have continuous data (a table).
Range("A1").CurrentRegion.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
'A trick to convert the formulas into values.
Range("A1").CurrentRegion.Value = Range("A1").CurrentRegion.Value
End Sub
The above table can be found in the file below. Just press the button, and the empty cells of the table will be filled with the suitable values. The sample values are related to welding maps from an industrial project.
Downloads
The workbook can be opened with Excel 2007 or newer. Please, enable macro before using it.
