Fill The Blank Cells Of A Table

Share this

July 22, 2011

Last updated: 30/09/2017, 1 min read

Fill The Blank Cells Of A Table


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


Download

The workbook can be opened with Excel 2007 or newer. Please, enable macro before using it.

Page last modified: January 6, 2019

Christos Samaras

Hi, I am Christos, a Mechanical Engineer by profession (Ph.D.) and a Software Developer by obsession (10+ years of experience)! I founded this site back in 2011 intending to provide solutions to various engineering and programming problems.

Christos E. Samaras

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
Add Content Block
>