Run this macro

Sub Fill_Blanks()
'by Dave Peterson 2004-01-06
'fill blank cells in column with value above
Dim wks As Worksheet
Dim rng As Range
Dim LastRow As Long
Dim Col As Long

Set wks = ActiveSheet
With wks
Col = ActiveCell.Column
'or
'col = .range("b1").column

Set rng = .UsedRange 'try to reset the lastcell
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set rng = Nothing
On Error Resume Next
Set rng = .Range(.Cells(2, Col), .Cells(LastRow, Col)) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If rng Is Nothing Then
MsgBox "No blanks found"
Exit Sub
Else
rng.NumberFormat = "General"
rng.FormulaR1C1 = "=R[-1]C"
End If

'replace formulas with values
With .Cells(1, Col).EntireColumn
.Value = .Value
End With

End With

End Sub


Gord

On Thu, 11 Sep 2008 01:47:39 -0700 (PDT), colwyn
wrote:

Gord, very many thanks for your help
Your code only works when there are numbers in all cells.
The column I use it on is column A. Now if the first block of data is
6 rows deep, cell A1 contains 1. The next number to appear will be 2
in cell A7. Cells A2:A6 are blank.
Is there a workaround for this in your code ??
Thanks again.
Colwyn.



On Sep 11, 1:27*am, Gord Dibben <gorddibbATshawDOTca wrote:
Sub InsertRow_At_Change()
'Sandy Mann July 1st, 2007
* * Dim LastRow As Long
* * Dim X As Long
* * LastRow = Cells(Rows.Count, 1).End(xlUp).Row
* * Application.ScreenUpdating = False

* * For X = LastRow To 3 Step -1
* * * * If Cells(X, 1).Value < Cells(X - 1, 1).Value Then
* * * * * * If Cells(X, 1).Value < "" Then
* * * * * * * * If Cells(X - 1, 1).Value < "" Then
* * * * * * * * * * Cells(X, 1).entirerow.Insert Shift:=xlDown
* * * * * * * * End If
* * * * * * End If
* * * * End If
* * Next X
* * Application.ScreenUpdating = True
End Sub

Assumes you have like values in Column A which designate the break in
blocks,

Gord

On Wed, 10 Sep 2008 15:17:10 -0700 (PDT), colwyn

wrote:

The spreadsheet contains a series of blocks of data and there are no
breaks between rows. I want to put a blank row between each block of
data. Any suggestions as to how I might go about this ??
It would be really helpful if I could achieve this end.
Thanks.
Colwyn.