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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.