<<Convert Excel Spreadsheets to Web Pages \| Trading Software That Operates Within Excel \| Convert Excel, Access & Other Databases \| Merge Excel Files>>

Microsoft Excel Formula and VBA Macro Code To Fill All Blank Cells In a List

| | Information Helpful? Why Not Donate.

TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL| More Free Downloads.. Best Value: Finance Templates Bundle

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 Instant Buy/Download,30 Day Money Back Guarantee & Free Excel Help for LIFE!

Got any Excel Questions? Excel Help. Do this painlessly over-and-over in seconds with the OzgridExcel List Manager

When setting up data in Excel it is good practice to ensure that all cells within a list are occupied. However, most people when creating a list in Excel will often leave a blank cell if the data for that cell is the same as the cell above, see example below

*Fruits*	*Cost*
Apple	$1.25
	$1.25
Banana	$2.55
	$2.55
	$2.55
Orange	$1.55
	$1.55
	$1.55
Strawberry	$4.55

While the Cost, in the right hand column, has the prices repeated, the Fruits are not. This is really NOT the correct way to set up data in Excel (see also: Efficient Excel Spreadsheet Design ). You will find you will encounter many problems when using such features as Excel Subtotals and Excel Pivot Tables to name only two. Excel expects, in most cases, for your related data to be set up in a continuous list or table.

Fill Blanks Via a Formula

Let's say you have a list of entries in column A, similar to the above example, and within the list you have many blank cells. Here is a quick and easy way to fill those blanks with the value of the cell above. Select all the data in column A, then go to Edit>Go To.... Ctrl+G and then click Special. Now check the Blanks option and click OK. This will now have selected only the empty cell within the list. Now push the Equals (=) key, then the Up arrow and finally, holding down the Ctrl key, push Enter.

You can quickly Convert Formulas to Value Only by selecting all of Column A, then copy (Ctrl+C) and then go to Edit>Paste Special, check Values then click OK.

Fill Blanks Via a Macro

If doing this this is going to be a frequent task you should consider a macro. The one below will make this a breeze. To use this, go to Tools>Macro>Visual Basic Editor (Alt+F11) then to Insert>Module and then paste in the code below.

Sub FillBlanks()Dim rRange1 As Range, rRange2 As RangeDim iReply As Integer    If Selection.Cells.Count = 1 Then        MsgBox "You must select your list and include the blank cells", _            vbInformation, "OzGrid.com"            Exit Sub    ElseIf Selection.Columns.Count > 1 Then        MsgBox "You must select only one column", _            vbInformation, "OzGrid.com"            Exit Sub    End If        Set rRange1 = Range(Selection.Cells(1, 1), _        Cells(65536, Selection.Column).End(xlUp))            On Error Resume Next    Set rRange2 = rRange1.SpecialCells(xlCellTypeBlanks)    On Error GoTo 0        If rRange2 Is Nothing Then        MsgBox "No blank cells Found", _            vbInformation, "OzGrid.com"        Exit Sub    End If        rRange2.FormulaR1C1 = "=R[-1]C"        iReply = MsgBox("Convert to Values", vbYesNo + vbQuestion, "OzGrid.com")    If iReply = vbYes Then rRange1 = rRange1.ValueEnd Sub

After pasting in the above code, click the top right X to get back to Excel and Save. Now go to Tools>Macro>Macros (Alt+F8) select FillBlanks and click Run, or Options to assign a shortcut key.

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALLpurchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Try out:Analyzer XL |Downloader XL |Smart VBA |Trader XL Pro (best value) |ConsoXL | MergeXL | O2OLAP for Excel | MORE>>

Excel Trader PackageTechnical Analysis in Excel With $139.00 of FREE software!

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft