PDA

View Full Version : Solved: AutoFill Macro



phendrena
03-12-2009, 08:15 AM
Hello There,

Would anyone please be able to help with an AutoFill macro?

I would like to take the text that is above the empty cell and fill copy it into the empty cell. It does this for each empty cell in columns A:B.

So it works down, finds a completed cell, fills the empty cells below it until it finds a completed cell and then fills the empty below that with that data etc etc.

Any suggestions?

Thanks,

chungtinhlak
03-12-2009, 09:06 AM
try this, i test it and it works. I'm sure someone here can come up with a better way to do it. replace the 50 to whatever your last row is.


Sub test()
Dim i As Integer
For i = 1 To 50
If Range("A" & i).Value <> "" And Range("A" & i + 1).Value = "" Then
Range("A" & i).Copy
Range("A" & i + 1).PasteSpecial
End If
If Range("B" & i).Value <> "" And Range("B" & i + 1).Value = "" Then
Range("B" & i).Copy
Range("B" & i + 1).PasteSpecial
End If
Next

End Sub

phendrena
03-12-2009, 09:12 AM
Brilliant!

Many thanks.

Kenneth Hobs
03-12-2009, 09:40 AM
You can remove the +1 if you don't want to add one more check below the last used cell in that column.
Sub FillBlanks()
Dim c As Range, r As Range, i As Long, lc As Long
With Worksheets("Sheet1")
For Each c In .Range("A:B").Columns
lc = .Cells(Rows.Count, c.Column).End(xlUp).Row
For i = 1 To lc + 1
Set r = .Cells(i, c.Column)
If IsEmpty(r) And r.Row <> 1 Then r.Value = r.Offset(-1).Value
Next i
Next c
End With
End Sub

Bob Phillips
03-12-2009, 09:42 AM
Here is another way, just for the fun of it, assuming that there are not formulae in that data.

Select the region, data and blanks

Edit>Goto>Special

Select the Constants button

OK out

Type the = sign, and then hit the up arrow, and then hit Ctrl-Enter

Select all the data again, anc copy it Ctrl-C

Edit>PasteSpecialand select Values button

OK out

phendrena
03-16-2009, 07:45 AM
Here is another way, just for the fun of it, assuming that there are not formulae in that data.

Select the region, data and blanks

Edit>Goto>Special

Select the Constants button

OK out

Type the = sign, and then hit the up arrow, and then hit Ctrl-Enter

Select all the data again, anc copy it Ctrl-C

Edit>PasteSpecialand select Values button

OK outOh thats mad!!! I like it :wot