Thread: Copy and Paste down the column macro

Every monday I need to prepare a lengthy spreadsheet for work. Part of the job involves copying and pasting the contents of a cell to the blank cells (in the same column) below the cell until it reaches the next cell that is not blank. Then I would do the same for that cell, and so on. Up until now I have been doing this by hand. I lose sleep Sunday nights dreading this. Does anyone know how to code a macro that could make my life much easier? Please help

John

Hi, Try this:-
The code is set for Columns "A to H" , alter to suit.
NB:- This should work unless you have a Blank in row (1)

Sub MG16Jul08
Dim Rng As Range, Dn As Range
Set Rng = Range(Range("A1"), Range("H" & Rows.count).End(xlUp))
    For Each Dn In Rng
        If Dn = vbNullString Then
            Dn = Dn.Offset(-1)
        End If
Next Dn
End Sub

Regards Mick

Mick,

Wow, thank you so much for your very quick reply. My fault for not explaining it well but I was looking to copy/paste down column A not over (to the R) in Row 1. Using what you gave me though I was able to write one with a very minor change for what i wanted to do. This is what I came up with:

Sub MG16Jul08()
Dim Rng As Range, Dn As Range
Set Rng = Range(Range("A1"), Range("A20" & Columns.Count).End(xlUp))
    For Each Dn In Rng
        If Dn = vbNullString Then
            Dn = Dn.Offset(-1)
        End If
Next Dn
End Sub

You have made my life soo much easier, THANK YOU MICK!!

John