Run script on cells in VBA Macro

In an Excel 2010 worksheet I want to build a Macro in VBA
I have a column with data from A2-A30000
I want to run a macro from top to bottom that is the current cell = "" set it to the cell above

I program in VB and am rusty in VBA syntax


So that starting at row 2

1002


1003

Would become
1002
1002
1002
1003
Larry Bristersr. DeveloperAsked:
Who is Participating?
 
nutschCommented:
Format your column as general, then run the macro, or update the macro as:

Sub CopyDownEmpty()
'This macro takes the selected range and for each empty cell, _
copies the value of the cell above
Dim cl As range, rng As range

Set rng = [a2:a30000]
columns(1).numberformat="General"

application.ScreenUpdating = False 'turns off screen updating



If application.WorksheetFunction.CountIf(rng, "") > 0 Then
    rng.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    rng.EntireColumn.Copy
    rng.EntireColumn.PasteSpecial xlPasteValues
Else
    MsgBox "No empty cells to fill"
End If

application.CutCopyMode = False

application.ScreenUpdating = True 'turns screen updating back on
End Sub

Open in new window

0
 
nutschCommented:
Something like this?

Sub CopyDownEmpty()
'This macro takes the selected range and for each empty cell, _
copies the value of the cell above
Dim cl As range, rng As range

Set rng = [a2:a30000]

application.ScreenUpdating = False 'turns off screen updating

If application.WorksheetFunction.CountIf(rng, "") > 0 Then
    rng.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    rng.EntireColumn.Copy
    rng.EntireColumn.PasteSpecial xlPasteValues
Else
    MsgBox "No empty cells to fill"
End If

application.CutCopyMode = False

application.ScreenUpdating = True 'turns screen updating back on
End Sub

Open in new window


Thomas
0
 
Larry Bristersr. DeveloperAuthor Commented:
nutsch:
That works when there's a number in the cell
But when theres text like "Bill" it inserver =R[-1]C
0
 
Larry Bristersr. DeveloperAuthor Commented:
Perfect, thanks
0
 
Larry Bristersr. DeveloperAuthor Commented:
Follow-up question coming
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.