CODEWITHSUNDEEP
vbaexcelexcel-2010
user1786695
user1786695

Reputation: 41

Create macro to move data in a column UP?

I have an excel sheet of which the data was jumbled: for example, the data that should have been in Columns AB and AC were instead in Columns B and C, but on the row after. I have the following written which moved the 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 doesn't fix the problem of the data being on the row BELOW the appropriate row now that they are in the right columns. I am new to VBA Macros so I would appreciate any help to make the data now align. I tried toggling the Offset parameter (-1,0) but it's not working.

Upvotes: 4

Views: 2202

Answers (2)

Jon Crowell
Jon Crowell

Reputation: 22338

You don't need to loop through the range to accomplish what you're 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 shape of data in each column is the same, you don't need to find the first and last row for each. You'll only need one variable for each and one copy operation instead of 2.
  • Make sure you set variable declaration to required. (Tools -> Options -> Require Variable Declaration) You may already be doing this, but I couldn't tell because it looks like the top of your Sub got truncated.

Upvotes: 0

PaulStock
PaulStock

Reputation: 11263

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

Upvotes: 1

Related Questions