copy

Friday, 22 July 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:        xristos.samaras@gmail.com
    'Site:          http://www.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.

Did you like this post? If yes, then share it with your friends. Thank you!


Categories:


Mechanical Engineer (Ph.D. cand.), M.Sc. Cranfield University, Dipl.-Ing. Aristotle University, Thessaloniki - Greece.
Communication: e-mail, Facebook, Twitter, Google+ and Linkedin. More info