Copy / Paste down

[exwarrior]

Gents I looked through about 15 pages of Posts and did not see anything that would satisfy my need for a macro. It seems to be a pretty simple one compared to some of the posts i looked through though. Any assistance would be extremely helpful

Ex: I have a page full of information with only the first 2 columns (A,B) being partially empty. I have a set of numbers that look like so:

Column A
xxxxxx




xxxxxx


Column B
xxxxxx
xxxxxx
xxxxxx
xxxxxx
xxxxxx
xxxxxx



Now i just need a simple macro that copies/pastes the contents of the first populated cell in column A down the column until there is another populated cell in the column. Then I need it to copy the new value and continue down the page in this fashion until there are no more values in A. The amount of rows between values in A will vary as well.

Thanks again for any help.

[Bob Phillips]

[vba]

Public Sub ProcessData()
Dim i As Long
Dim iLastRow As Long
Dim iStart As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
iStart = 1
For i = 2 To iLastRow
If .Cells(i, "A").Value <> "" Then
If i > iStart + 1 Then
.Cells(iStart, "A").AutoFill .Cells(iStart, "A").Resize(i - iStart)
End If
iStart = i
End If
Next i
End With

End Sub
[/vba]

[exwarrior]

Simply outstanding. Good things come from Texas i see.

[lucas]

better take a closer look at that flag...

[exwarrior]

Nice. It only took me 2 posts to get my foot into my mouth.

[exwarrior]

It works great with one exception: the cells that I'm copying down end in numbers and when it pastes down the column it counts thus not actually pasting the true value that I need it to.

[Bob Phillips]

Sorry, can you explain that another way?

[exwarrior]

The cells in my Column are numeric values. So with every row down the column the value of the cell increases.

[Bob Phillips]

[vba]

Public Sub ProcessData()
Dim i As Long
Dim iLastRow As Long
Dim iStart As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
iStart = 1
For i = 2 To iLastRow
If .Cells(i, "A").Value <> "" Then
If i > iStart + 1 Then
.Cells(iStart + 1, "A").Resize(i - iStart - 1).Value = .Cells(iStart, "A").Value
End If
iStart = i
End If
Next i
End With

End Sub
[/vba]

[exwarrior]

Works like a charm now. Thank you immensely.

[jolivanes]

Nice.
I have been working on a similar issue on and off for a while. The difference is that mine needs to work the opposite way, in other words from the last row upwards to the last cell in row 12. I tried to change your code xld but I butchered it so bad, I had to erase it all again. So far no luck.
If it is not too much trouble, I would sure appreciate the right way.
Thanks and regards.
John

[jolivanes]

BTW, this si what I currently have.

Range("I65536").End(xlUp).Offset(0, 2).Select
 If ActiveCell.Value = "" Then
 ActiveCell.Value = "0.00"
 End If
    'Put this value in memory
    TF = ActiveCell.Value
        'Select the cell above the last cell
        ActiveCell.Offset(-1, 0).Select
    Do
    'If the active cell is empty and the cell to the left of the active cell _
     has a value, then fill the cell with the TF value
    If IsEmpty(ActiveCell) And IsEmpty(ActiveCell.Offset(0, -1)) = False Then
        ActiveCell.Value = TF
            'Move one cell up
            ActiveCell.Offset(-1, 0).Select
                Else
                    'If both the active cell and the cell to the left are empty, move _
                     one cell up
                    If IsEmpty(ActiveCell) And IsEmpty(ActiveCell.Offset(0, -1)) Then
                        ActiveCell.Offset(-1, 0).Select
                            Else
                                'If the active cell has a value, store this value _
                                 and move one cell up
                                If IsEmpty(ActiveCell) = False Then
                                    TF = ActiveCell.Value
                                        ActiveCell.Offset(-1, 0).Select
                                End If
                    End If
    End If
 Loop Until IsEmpty(ActiveCell.Offset(-1, -2)) And IsEmpty(ActiveCell.Offset(-4, -2))

[Bob Phillips]

I am not sure of how you want it to work.

Can you post an example workbook with exampl results?

[jolivanes]

Hi xld
Thank you for your help.
Attached is a worksheet with "Before" and "After" sheets and the explanation.
Hope I explained it well enough.
Thanks and Regards.
John

[Bob Phillips]

I have assumed that columns J and K are synchronised, if one is empty so is the other and vice versa.

[vba]

Public Sub ProcessData()
Const FIRST_ROW As Long = 12
Dim i As Long
Dim iLastRow As Long
Dim iStart As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = iLastRow To FIRST_ROW Step -1
If .Cells(i, "J").Value <> "" Then
iStart = i
ElseIf .Cells(i - 1, "J").Value <> "" Then
.Cells(i, "J").Resize(iStart - i + 1, 2).FillUp
End If
Next i
If .Range("J12").Value = "" Then
.Range("J12").Resize(iStart - FIRST_ROW + 1, 2).FillUp
End If
End With

End Sub
[/vba]

[jolivanes]

Hi Bob (It is Bob isn't it?)
Thank you very much. Works perfect.
Regards.
John

[Bob Phillips]

Yes it is.

Glad it is sorted.