Excel Macro

Euphoriasean

I have an excel sheet with around 1000 rows and I have inserted blank rows in between. I want to copy the data from B2 into A3 and continue this down the spreadsheet. Its been a while since I used macros,ive a pretty basic knowledge too. Could someone provide some code I could use.

Thanks

pillphil

Not sure I'm reading this right, you have this?



And you want this?


Put

=(B2)

in A2 and drag it down?

Seth Brundle

It depends on whether you want to do it all in one go via Copy/Paste or do it line by line...

Sub CopyDown()
    Range("B2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("A3").Select
    ActiveSheet.Paste
End Sub

Sub LineByLine()
    Dim i As Integer
    For i = 2 To ActiveSheet.Range("B65536").End(xlUp).Row    
        
        Range("A" & i + 1).Value = Range("B" & i).Value
    Next
End Sub

croo

@pillphil a little typo there I think?

pillphil wrote: »

in A2 and drag it down?

You meant in A3 right?

pillphil

Oops

Euphoriasean

pillphil wrote: »

Not sure I'm reading this right, you have this?



And you want this?


Put

=(B2)

in A2 and drag it down?

Thanks for the reply. The problem with this is I have data in a2,a4,a6 etc that I do not want to remove. If I copy down all these cells will be blank.

Euphoriasean

kbannon wrote: »

It depends on whether you want to do it all in one go via Copy/Paste or do it line by line...

Sub CopyDown()
    Range("B2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("A3").Select
    ActiveSheet.Paste
End Sub

Sub LineByLine()
    Dim i As Integer
    For i = 2 To ActiveSheet.Range("B65536").End(xlUp).Row    
        
        Range("A" & i + 1).Value = Range("B" & i).Value
    Next
End Sub

Thanks. The line by line code works fine but as per my post above I have data in a2,a4,a6 etc. This again will leave them blank. I need to run a macro that can copy data from say a3 to b2 then b4 to a5 then b6 to a7 etc. Apologies if I am not explaining myself very well.

pillphil

There's definitely a better way to do this

You have this?


new column and formula


Other formula


Highlight both


Drag as far as needed


You'll need to keep both column or you can copy the value of the new column and paste it over itself and remove the original columns

Euphoriasean

Snip 2.JPG

This may give a better description of what I am trying to do.

pillphil

My second attempt will do that in an albeit hilariously convoluted manner

Euphoriasean

pillphil wrote: »

There's definitely a better way to do this

You have this?


new column and formula


Other formula


Highlight both


Drag as far as needed


You'll need to keep both column or you can copy the value of the new column and paste it over itself and remove the original columns

Yes I am a dumb a**. Thanks that is exactly what I am trying to do. Leave it to me to try and overcomplicate things.