Link to home
Start Free TrialLog in
Avatar of John Smith
John SmithFlag for Australia

asked on

How can I copy data and paste into every second Down

I have a macro which works by copying data in a cell in column "A" then paste the data into every second row until new data is in a cell. This code starts from the last row but I need to change the operation to start at the top of the column "A11" then run down wards.
Could some cahnge the code to operate down the column instead of up the column, Please.
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

If I have understood your problem correctly you just need these two lines of code

Range("B:B").SpecialCells(xlCellTypeConstants).Offset(, -1).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=r[-2]c"
Range("B:B").Value = Range("B:B").Value
This looks like an easy thing made difficult. Why not simplify it?
You would like to insert data in every other row. That throws up 2 questions.
1. Which data?
Your worksheet and your write-up are too hypothetical where they should be generic.
The data you wish to multiply are, presumably, in a cell. You should either name or describe the cell, such as Cell A10, or the first cell in column A that has a value in it, or the first cell in column A below A10 that has the value in it that is equal to, say, B3.
2. Which rows?
You say that you wish to start at A11. Is A11 the first blank or the first filled cell?
How far down do you wish to repeat the data? Until a non-blank cell is encountered?
Can this result in there not being a blank cell below the last multiplied data?
In all of this "data" refers to the contents of a single cell. Your sample seems to suggest that you have multiple cells to copy from. Please clarify.
Avatar of Anthony Berenguel
Anthony Berenguel
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of John Smith


Comments 1 and 3

Haved most appreciated I have tried comment 3 and it works perfectly. Many Thanks
Did you try #1?
Hi ssaqibh

YES, I have tried your code and its works great, below is the working code.

Sub FindAddDataX2RowC_B()
Application.ScreenUpdating = False
Dim rng As Range
        Range("B:B").SpecialCells(xlCellTypeConstants).Offset(, -1).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=r[-2]c"
        Range("B:B").Value = Range("B:B").Value
Application.ScreenUpdating = True
End Sub

Sorry, for the delay but work commitments prevented me from testing. Appreciate you experts your most helpful, THANKS.
The statement

Dim rng As Range

is redundant as no variable is being used in the code. You can delete this line.

The other two screen updating statements may also be deleted without any noticable change as the program has to execute only two statements.