• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1352
  • Last Modified:

Excel VBA Copy previous row cell value

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?
2 Solutions
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!!

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
Chrissim16Author Commented:
@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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now