Fill Excel Blank Rows Macro

April 23, 2002 - by Bill Jelen

Thanks to Mike who provided this week's Excel question:

I'm trying to write a macro and am new at this. I have an Excel spreadsheet that is hundreds of pages long and many columns wide. I would like the macro to search only the "A" column for ""(no data) and then use the data from the previous row, selecting from columns "A" through "CB" and then fill down until it senses data in a cell in the "A" column. Then it needs to repeat until all has been done.

The macro below will do the trick:

Public Sub CopyDown()
    LastRow = Range("A65536").End(xlUp).Row
    For i = 1 To LastRow
        If Range("A" & i).Value = "" Then  
		Range("A" & i - 1 & ":CB" & i - 1).Copy Destination:=Range("A" & i)
	End if
    Next i
End Sub

But you may not have to use a macro to do what you want. A quicker way is to use the tricks introduced in tip from 12/12/98 to accomplish the same thing without a macro.

The first trick is to select all of the blank cells in your selection. Highlight from A1 through CB in the last row of your data. Select Edit > GoTo > Special > Blanks > OK.

Next, you want to enter the same formula in all of those blank cells. To do this, hit the equals key, hit the up arrow, then hold down Ctrl and hit Enter. This will replicate the formula to all of the blank cells.

Finally, change the formulas to values. Highlight the entire range of data, select Edit > Copy, Edit > Paste Special > Values > OK.

Note that if Mike's original data contained formulas or contained some rows that were partially blank, then this procedure will not work, it would be better to stick with the macro shown above.