How to paste data?

R

Rick

Is this what you need?

Sub MoveDownData()
Dim S1 As Worksheet: Dim rng As Range
Dim Cel As Variant: Dim EndRow As Long

Set S1 = Sheets("Sheet1")
EndRow = S1.Range("C65536").End(xlUp).Row

Set rng = S1.Range(Cells(1, 3), Cells(EndRow + 1, 3))

For Each Cel In rng
If Cel.Value = "" Then
Cel.Value = "Happy Holidays"
Exit For
End If
Next Cel

End Sub
 
D

David

Start from the top of the range(the first cell with
data). This will find the bottom, then select and copy
the range and put it directly below the original data.

Sub Macro1()
Top = ActiveCell.Address
Selection.End(xlDown).Select
Bottom = ActiveCell.Address
Range((Top) & ":" & (Bottom)).Select
Selection.Copy
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub
 
Top