Copy/Paste Excel Macro

[Justin]

Hello,

What I'm trying to do is automate a process using an excel macro. What
I need the macro to do is to look for a blank cell in a range of data
in Column A, and whenever there is a blank space within that range,
paste some specific above information into this row. (I'm formatting
an excel file after taking it from another program). I would greatly
appreciate any help!

Thank you!

 

[David]

You could record a macro to filter on blank in coloum A then paste a formula
+cell above- this would then fill in your blanks

 

[Gord Dibben]

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 = .Range("A1").column
'or
'Col = ActiveCell.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.NumberFormat = "General"
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 MS Excel MVP

 

[JLGWhiz]

Your description of the problem is too vague for suggesting code that will
do the job. But here is a sample that would do some of what you describe:

Sub fillBlanks()
Dim lr As Long, rng As Range
Dim sh As Worksheet, c As Range
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh.Range("A1:A" & lr)
For Each c In rng
If c.Row <> 1 Then
If c.Value = "" And c.Offset(-1, 0) > "" Then
c.Value = c.Offset(-1, 0).Value
End If
End If
Next
End Sub

 

[Justin]

THANK YOU SO MUCH!! THIS GREATLY HELPED ME AT WORK! With a little
modification, I was able to get it to copy all the cells I need in
just one macro!

THANKS AGAIN!
-JUSTIN