Trying to simplify data in Excel through VBScript

Solved/Closed
ColGood Posts 2 Registration date Monday May 1, 2017 Status Member Last seen May 5, 2017 - Updated on May 1, 2017 at 02:45 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - May 8, 2017 at 11:00 AM
Please help desperate novice:

In Excel, I have 5 columns:

Bob Dad 12 13 blank
Sue Mum 34 56 22
Gill Daughter 23 67 34
Jock Son 24 55 blank

I want it simplified for reference purposes to three columns:

Bob Dad 12
Bob Dad 13
Sue Mum 34
Sue Mum 56
Sue Mum 22
Gill Daughter 23
Gill Daughter 67
Gill Daughter 34
Jock Son 24
Jock Son 55

Any ideas. I've tried many, many things but am getting in a bit of a mess....

Any help will be gratefully appreciated. I know I'm a numpty but this seems really hard!
Related:

1 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
May 4, 2017 at 11:01 AM
Hi ColGood,

Give the following code a try:
Sub RunMe()
Dim x As Integer
x = 1
Do
    If Range("D" & x).Value <> vbNullString Then
        x = x + 1
        Rows(x).Insert
        Range("A" & x).Value = Range("A" & x - 1).Value
        Range("B" & x).Value = Range("B" & x - 1).Value
        Range("C" & x).Value = Range("D" & x - 1).Value
    End If
    If Range("E" & x - 1).Value <> vbNullString Then
        x = x + 1
        Rows(x).Insert
        Range("A" & x).Value = Range("A" & x - 1).Value
        Range("B" & x).Value = Range("B" & x - 1).Value
        Range("C" & x).Value = Range("E" & x - 2).Value
    End If
    x = x + 1
Loop Until Range("A" & x) = vbNullString

Columns("D:E").ClearContents

End Sub


How to implement and run a code:

- From Excel hit Alt + F11 to open the “Microsoft Visual Basic” window.
- Go to the top menu in the newly opened window > Insert > Module.
- Paste the code in the big white field.
- You can now close this window.
- Back at Excel, hit Alt + F8 to display the available macro’s.
- Double-click the macro you wish to run.
NOTE: macro’s cannot be reversed using the blue arrows. Always make sure you save your file (or create a back up to be entirely sure) before running a code, so you can re-open your file if something unforeseen happens or you want to go back to the situation before the code was run.

Best regards,
Trowa
0
ColGood Posts 2 Registration date Monday May 1, 2017 Status Member Last seen May 5, 2017
May 5, 2017 at 12:54 PM
This is amazing! Thank you so much! Works like a dream. You have no idea how long I've been trying to do that! Thanks
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
May 8, 2017 at 11:00 AM
Awesome! Great to be able to help you out.
0