plz help:- a simple macro.

S

sokevin

a simple macro.


i want to auto fill/copy down the data that is in a2 untill the nex
blank.say this is a5. so a2 to a5 is all the same.


then it will do the same for the next block of data. A7 to A11



i have about 1000 of these to do, thats why doing it manually is to
hard.


thanks for any assistance.

p.s the numbers is cloumn A are of not any use, so they can be copie
over.

cheers

kev


i have included pics to show, before and after.


http://www.imagestation.com/picture/sraid132/p293bb6eeaa519963a0b59f45c4cb942f/f79741ce.jpg



http://www.imagestation.com/picture/sraid132/p315791ec00aa884ac613dc77ea73f5de/f79741cb.jp
 
G

Gord Dibben

Don't neeed a macro.

Select column A and Edit>Go To>Special>Blanks>OK

Enter and = sign in the active cell then mouse-click on cell above and CTRL +
ENTER to fill down.

If macro desired..........

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.FormulaR1C1 = "=R[-1]C"
End If

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

End With

End Sub

Gord Dibben Excel MVP
 
Top