Fill Down Blank Cells in Table via VBA

[jski21]

Good Day Mr. Excel Team,

Ran across a lousy download from a central source (govt) I'll need on a regular basis that does not follow basic database courtesy. I'm seeking to have a snippet of code that would work its way through a table, locating a blank cell, and then copying down the data immediately above to all the blank cells below. The spreadsheet can get rather voluminous, hence the ask for some expertise. So far, I'm only seeing instances of this in Column 1 and Column 7.

Column 1	Column 2	Column 3	Column 4	Column 5	Column 6	Column 7	Column 8	Column 9	Column 10	Column 11	Column 12	Column 13	Column 14	Column 15
165629	1	2	2	1/14/1998	Conv-Sum	01/14/1998	1/14/1998	ABCPDQ1234	1982	EN	123456789	123456789	ABCD	$32,371,000.00
165630	1	2	2	1/14/1998	Conv-Sum	01/14/1998	1/14/1998	ABCPDQ1234	1989	EN	123456789	123456789	ABCD	$23,796,000.00
165631	1	2	2	1/14/1998	Conv-Sum	01/14/1998	1/14/1998	ABCPDQ1234	1990	EN	123456789	123456789	ABCD	$22,691,000.00
165632	1	2	2	1/14/1998	Conv-Sum	01/14/1998	1/14/1998	ABCPDQ1234	1991	EN	123456789	123456789	ABCD	$25,108,000.00
165633	1	2	2	1/14/1998	Conv-Sum	01/14/1998	1/14/1998	ABCPDQ1234	1992	EN	123456789	123456789	ABCD	$26,382,000.00
165634	1	2	2	1/14/1998	Conv-Sum	01/14/1998	1/14/1998	ABCPDQ1234	1993	EN	123456789	123456789	ABCD	$30,251,000.00
165635	1	2	2	1/14/1998	Conv-Sum	01/14/1998	1/14/1998	ABCPDQ1234	1994	EN	123456789	123456789	ABCD	$33,571,000.00
165636	1	2	2	1/14/1998	Conv-Sum	01/14/1998	1/14/1998	ABCPDQ1234	1995	EN	123456789	123456789	ABCD	$33,604,000.00
165637	1	2	2	1/14/1998	Conv-Sum	01/14/1998	1/14/1998	ABCPDQ1234	1996	EN	123456789	123456789	ABCD	$32,571,000.00
165638	1	2	2	1/14/1998	Conv-Sum	01/14/1998	1/14/1998	ABCPDQ1234	1997	EN	123456789	123456789	ABCD	$6,036,449.72
165640	1	1	5	1/14/1998	Conv-Sum	01/14/1998	1/14/1998	ABCPDQ1234	1992	EN	123456789	123456789	EFGH	$42,557.00
165641	1	1	6	1/14/1998	Conv-Sum	01/14/1998	1/14/1998	ABCPDQ1234	1992	EN	123456789	123456789	EFGH	$11,800.00
165642	1	1	7	1/14/1998	Conv-Sum	01/14/1998	1/14/1998	ABCPDQ1234	1992	EN	123456789	123456789	EFGH	$29,500.00
165981	1	1	508	1/14/1998	Conv-Sum	01/14/1998	1/14/1998	ABCPDQ1234	1993	EN	123456789	123456789	EFGH	$11.00
165982	1	1	670	1/14/1998	Revised		1/14/1998	ABCPDQ1234	1993	EN	123456789	123456789	EFGH	$17,005.00
	2	1	173	10/4/2001	Conv-Sum		1/14/1998	ABCPDQ1234	1993	EN	123456789	123456789	EFGH	$0.00
	3	1	670	10/4/2001	Revised		1/14/1998	ABCPDQ1234	1993	EN	123456789	123456789	EFGH	$17,005.00
	4	1	173	10/4/2001	Conv-Sum		1/14/1998	ABCPDQ1234	1993	EN	123456789	123456789	EFGH	$17,005.00
	5	1	670	10/4/2001	Conv-Sum		1/14/1998	ABCPDQ1234	1993	EN	123456789	123456789	EFGH	$0.00

Many thanks in advance for the review, consideration, and guidance.

jski

 

[DanteAmor]

Try this:

Sub filldown()
  Dim lr As Long, lc As Long
 
  lr = Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
  lc = Cells(1, Columns.Count).End(1).Column
  With Range("A1", Cells(lr, lc))
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
  End With
End Sub

You may need to adjust the format of the column so that the new values take it into account.

 

[jski21]

Try this:

Sub filldown()
  Dim lr As Long, lc As Long
 
  lr = Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
  lc = Cells(1, Columns.Count).End(1).Column
  With Range("A1", Cells(lr, lc))
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
  End With
End Sub

You may need to adjust the format of the column so that the new values take it into account.

Beautiful, accurate, and quick. Thank you DanteAmor!