Warning: date(): It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected the timezone 'UTC' for now, but please set date.timezone to select your timezone. in /usr/www/users/mrexcel/website_configuration.php on line 10

Your One Stop for Excel Tips & Solutions


MrExcel - Photos of MrExcel

Run a Macro to Find and Fill in the Blank Rows in an Excel Spreadsheet

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.

Warning: mysql_connect(): Access denied for user 'mrexcel_22_w'@'localhost' (using password: YES) in /usr/www/users/mrexcel/siteadmin/admincp/config.php on line 8
Could not connect