Macro copy paste from row above loop until last non empty row

[jmoe007]

hello all.

Looking for a function to auto fill (copy paste) from the row above and skip rows that have data in them until the last non empty row and cant seem to find the right one.

Basicly would like to high light an entire column then start a macro that will copy the cell from above(row) to cell below(row until next cell that has data, continue down (skip rows with data) until next empty cell and copy the row above, loop that process until the end of the column or last row that has data in it.

There are a lot of great posts but cant seem to find that will do this.


<TABLE style="WIDTH: 186pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=248><COLGROUP><COL style="WIDTH: 100pt; mso-width-source: userset; mso-width-alt: 4864" width=133><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><TBODY><TR style="HEIGHT: 28.5pt; mso-height-source: userset" height=38><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 100pt; HEIGHT: 28.5pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=38 width=133>Before Macro</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 86pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=115>After Macro</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20>apple</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">apple</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20>apple</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">apple</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">apple</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20>carrot</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">carrot</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">carrot</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20>car</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">car</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20>car</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">car</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">car</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">car</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">car</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">car</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20>car</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">car</TD></TR></TBODY></TABLE>
end loop at last non empty row

Thanks
Joe.....

 

[mtskv5]

This will get you there -- just create the part where you select the range.

Sub New_Data_Box()
    
'To create a new gray data box for the new mnth
    
    Dim CI As String
    Dim RC As Integer
    Dim Counter As Integer
    Counter = 0
    RC = 2
    
        Range("A1").Select
        CI = Range("A1").Value
        
        For RC = 1 To 12 Step 1
            If Cells(RC, 1).Value = CI Then
                If Cells(RC + 1, 1).Value = CI Then
                    
                Else
                    If Cells(RC + 1, 1).Value = "" Then
                        Cells(RC + 1, 1).Value = CI
                    End If
                End If
            Else
                CI = Cells(RC, 1).Value
                If Cells(RC + 1, 1).Value = "" Then
                    Cells(RC + 1, 1).Value = CI
                End If
            End If
        Next RC
    
End Sub

 

[Norie]

This should work but can be a bit temperamental.

Dim rng As Range
 
Set rng = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
 
rng.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"

rng.Value = rng.Value

 

[jmoe007]

Thanks Norie.. awesome

Dim rng As Range

Set rng = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)

rng.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"

rng.Value = rng.Value

question.? any way to change the Range from a set cell to the active cell or range..

so in effect i would not have to alter the marco just highlight column that i wanted to up date and run it.. kind of like a range = equal active column or cell?

Thanks jason