Using VBA for the first time

[New_at_this]

I have been given a task where I need to manipulate a lot of data (90k or so rows of information) in excel and have worked out that using VBA and Macros is the best way to do this. Unfortunately, I do not have any computer qualifications and my use of excel has peaked at using Pivot Tables!

I need to change data from the format

Row/Column A B
1 RABBIT 123
2 RABBIT 887
3 RABBIT 455
4 PIG 336
5 PIG 723

(but with columns A-F instead of A-B)

I want this to look like:
Row/Column A B C D
1 RABBIT 123 887 455
2 PIG 336 723

I have found a script online that I thought might work but got an error message when I tried it. Can anyone help me out?

 

[MrKowz]

Welcome to the forums!

What do you mean by "(but with columns A-F instead of A-B)"?

 

[New_at_this]

I have data in columns A to F rather than just A & B that I used in my example.

 

[MrKowz]

Try this out on a copy of your data:

Public Sub ConsolidateColA()
Dim i   As Long, _
    LR  As Long, _
    sLC As Long, _
    dLC As Long
LR = Range("A" & Rows.Count).End(xlUp).row
Application.ScreenUpdating = False
For i = LR To 2 Step -1
    Application.StatusBar = i & " rows left to check."
    If Range("A" & i - 1).Value = Range("A" & i).Value Then
        sLC = Cells(i, Columns.Count).End(xlToLeft).Column
        dLC = Cells(i - 1, Columns.Count).End(xlToLeft).Column + 1
        Range(Cells(i, 2), Cells(i, sLC)).Copy Destination:=Cells(i - 1, dLC)
        Rows(i).Delete Shift:=xlUp
    End If
Next i
With Application
    .ScreenUpdating = True
    .StatusBar = False
End With
End Sub

 

[New_at_this]

Will do.

Thanks

 

[New_at_this]

That worked perfectly. Once again, thanks!

As an aside, how on earth do you get to learn how to code things like this? Is there a series of courses that you can go on?

 

[MrKowz]

I took a couple of small courses at my university to learn the very basics of VBA programming and Excel formulas, but though an apt for programming and reading these forums a lot, I've taught myself loads more than any course could really ever teach. Just ask questions, pick apart code, and try things.