Create a macro to move data in an UP column?

Question

I have an excel sheet whose data was mixed: for example, the data that should have been in columns AB and AC was instead in columns B and C, but in the row after. I have the following written: they moved data from B and C to AB and AC, respectively:

Dim rCell As Range Dim rRng As Range Set rRng = Sheet1.Range("A:A") i = 1 lastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row For Each rCell In rRng.Cells If rCell.Value = "" Then Range("AB" & i) = rCell.Offset(0, 1).Value rCell.Offset(0, 1).ClearContents End If i = i + 1 If i = lastRow + 1 Then Exit Sub End If Next rCell End Sub 

However, it does not fix the problem of the data in the row BELOW the corresponding row, now that they are in the correct columns. I am new to VBA Macros, so I would appreciate any help so that the data is now aligned. I tried to switch the Offset parameter (-1.0), but it does not work.

Answer 1

Try something like this?

 For i = Lastrow To 1 Step -1 ' move data into cell AA from Cell A one row down Cells(i, 27).Value = Cells(i + 1, 1).Value Next 

Answer 2

You do not need to iterate over the range to accomplish what you are trying to do.

Try this instead:

 Sub MoveBCtoAbAcUpOneRow() Dim firstBRow As Integer Dim lastBRow As Long Dim firstCRow As Integer Dim lastCRow As Long ' get the first row in both columns If Range("B2").Value <> "" Then firstBRow = 2 Else firstBRow = Range("B1").End(xlDown).Row End If If Range("C2").Value <> "" Then firstCRow = 2 Else firstCRow = Range("C1").End(xlDown).Row End If ' get the last row in both columns lastBRow = Range("B" & ActiveSheet.Rows.Count).End(xlUp).Row lastCRow = Range("C" & ActiveSheet.Rows.Count).End(xlUp).Row ' copy the data to the correct column, up one row Range("B" & firstBRow & ":B" & lastBRow).Copy Range("AB" & firstBRow - 1) Range("C" & firstCRow & ":C" & lastCRow).Copy Range("AC" & firstCRow - 1) ' clear the incorrect data Range("B" & firstBRow & ":B" & lastBRow).ClearContents Range("C" & firstCRow & ":C" & lastCRow).ClearContents End Sub 

Notes:

• If the data form in each column is the same, you do not need to find the first and last row for each. You will need only one variable for each and one copy operation instead of 2.
• Make sure you specify a variable declaration. (Tools → Options → Require Variable Declaration) You may already be doing this, but I couldn’t say it because it looks like the top of your Sub has been truncated.