Macro to fill blank cells in excel

armymom13
armymom13 used Ask the Experts™
on
HI experts
I am in need of a macro that will copy the ID in column A to the blank cells beneath it…. In other words
Copy A3 paste to A4 - A9
Copy A11 paste to A12 - A19
Can anyone help with this…
Ataching example spreadsheet.. thanks

need-macro-to-fill-blanks.xls.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I can't think of a macro, but select the two cells with the same number.
you will see a dot on the bottom right of the cells.
Drag it down to the last cell that you want to fill. It will copy the exact number from your ID cell.
Expert of the Quarter 2010
Expert of the Year 2010
Commented:

Sub fillmedown()
Dim r As Range, r2 As Range
Set r = ActiveWorkbook.ActiveSheet.Range("A1")
While r.Row < r.Worksheet.UsedRange.Rows.Count
Set r = ActiveWorkbook.ActiveSheet.[A:A].Find("", r, xlValues)
If Not r Is Nothing Then
Set r2 = r.End(xlDown).Offset(-1)
If r2.Row >= r2.Worksheet.Rows.Count - 1 Then
Set r2 = r2.Offset(, 1).End(xlUp).Offset(, -1)
End If
If r2.Row >= r.Row Then
Range(r, r2).Value = r.Offset(-1).Value
End If
End If
Wend
End Sub

Open in new window

Author

Commented:
Thanks a bunch... I knew there was one, just couldn't remember how to word it to find it in the db..
Most Valuable Expert 2011
Awarded 2010

Commented:
Another non-macro suggestion:

Select Column A
Hit F5
Click Special
Tick Blanks
Hit OK
enter = followed by the up arrow
Hold down Ctrl and hit enter

If you want, copy column A and use Paste special - Values to paste the data over the formulas

cheers, teylyn
Most Valuable Expert 2011
Awarded 2010

Commented:
My suggestion done with the macro recorder switched on results in this:

cheers, teylyn
Sub Macro1()
    Columns("A:A").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.FormulaR1C1 = "=R[-1]C"
    Columns("A:A").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A2").Select
End Sub

Open in new window

Commented:
CREATE NEW MACRO AND COPY THIS CODE

Dim i As Integer
Dim p As Integer
Dim Cell As String
Dim CELLF As String


' skip first cells with column name
' enter number of first NOT EMPTY cell
i = 2

'check all cells from 1 to 50 or more just change i value
Do Until i = 100
Cell = "A" & i
Range(Cell).Select
'check if cell is empty
If ActiveCell.Value > 0 Then
p = i
CELLF = "A" & p
GoTo 10
End If
' if empty copy previous CELLF
Range(CELLF).Copy Range(Cell)

10
i = i + 1
Loop
End Sub

' good luck




change i as you need (starting and ending cell in your workbook)

Dim i As Integer
Dim p As Integer
Dim Cell As String
Dim CELLF As String


' skip first cells with column name
' enter number of first NOT EMPTY cell
i = 2

'check all cells from 1 to 50 or more just change i value
Do Until i = 100
Cell = "A" & i
Range(Cell).Select
'check if cell is empty
If ActiveCell.Value > 0 Then
p = i
CELLF = "A" & p
GoTo 10
End If
' if empty copy previous CELLF
Range(CELLF).Copy Range(Cell)

10
i = i + 1
Loop

' good luck

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial