PDA

View Full Version : Copy / Paste down



exwarrior
10-16-2007, 10:18 AM
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 :yes

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
10-16-2007, 10:43 AM
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

exwarrior
10-16-2007, 10:52 AM
Simply outstanding. Good things come from Texas i see.

lucas
10-16-2007, 11:14 AM
better take a closer look at that flag...

exwarrior
10-16-2007, 11:54 AM
Nice. It only took me 2 posts to get my foot into my mouth.

exwarrior
10-16-2007, 01:27 PM
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
10-16-2007, 01:43 PM
Sorry, can you explain that another way?

exwarrior
10-16-2007, 01:52 PM
The cells in my Column are numeric values. So with every row down the column the value of the cell increases.

Bob Phillips
10-16-2007, 02:06 PM
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

exwarrior
10-17-2007, 05:58 AM
Works like a charm now. Thank you immensely.

jolivanes
10-22-2007, 12:51 PM
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
10-22-2007, 12:59 PM
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
10-22-2007, 01:14 PM
I am not sure of how you want it to work.

Can you post an example workbook with exampl results?

jolivanes
10-22-2007, 07:24 PM
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
10-23-2007, 04:28 AM
I have assumed that columns J and K are synchronised, if one is empty so is the other and vice versa.



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

jolivanes
10-23-2007, 08:37 AM
Hi Bob (It is Bob isn't it?)
Thank you very much. Works perfect.
Regards.
John

Bob Phillips
10-23-2007, 08:45 AM
Yes it is.

Glad it is sorted.