Excel VBA Copy previous row cell value

Posted on 2011-04-21
Last Modified: 2012-05-11
Hi there,

I have an Excel file where in the B column the uneven row cells have a value but the even row cells do not have value. I would like to copy the value of the uneven cell to the even cell. For example, B2= B1, B4=B3,B6=B5 etc. It should also look solely at this range, not at the whole sheet. I need a VB solution for this.  I tried to record a macro using the special values (F5) for blanks, but then I get a circular reference. Anyone got an idea?
Question by:Chrissim16
    LVL 2

    Accepted Solution

    just write this!

    Sub copyoddtoeven()
    Set cur_cell = Range("B1")
    While (cur_cell <> "")
    cur_cell.Offset(1, 0) = cur_cell
    cur_cell = "" 'to clear the value in the odd row, if not required, disable this line
    Set cur_cell = cur_cell.Offset(2, 0)
    End Sub

    let me know if you are facing any issue!!

    LVL 1

    Assisted Solution

    You can try this too. Hope it helps.

    Sub OddToEven()

    Dim column As String 'Column in which values lie
    Dim start As Integer 'Start of block
    Dim final As Integer 'End of block
    Dim incr As Integer  'Increment to row

    column = "B"
    start = 2
    final = 6
    incr = 1

    For i = start To final
        Range(column & Trim(Str(i - 1))).Value = Range(column & Trim(Str(i))).Value
        i = i + incr

    End Sub

    Author Comment

    @Bala: Thanks, it worked perfectly!!
    @ShivaKK: Yours almost worked perfectly, but it dod not copy the value for the last row, but thanks very much for help

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Introduction This Article is a follow-up to my Mappit! Addin Article (, it was inspired by an email posting I made to EUSPRIG (, I will briefly cover: 1) An overvie…
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now