Copy/Paste Excel Macro

  • Thread starter Thread starter Justin
  • Start date Start date
J

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!
 
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
 
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
 
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
 
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
 
Back
Top