Excel VBA - Split one row data in mutiple rows

Question

I am trying to take an excel file, look at the columns which have been filled and then split data in one excel row into various rows and in the process eliminate certain columns using VB. I think it will make more sense if you look at the images below.

Below is the image of what I am trying to split.

Before

Below is the image of how I want it split.

After

If someone can help me with it's VB code that would be great. I am new to VB and trying to learn.

Have you already attempted to write a piece of code that you could share? — VBA Pete
– VBA Pete, Commented Jan 12, 2017 at 22:53

VBA Pete · Accepted Answer · 2017-01-12 23:22:38Z

1

How about the following code:

Sub Format()

lastrow = ActiveSheet.UsedRange.Rows.Count

For x = lastrow To 2 Step -1

    If Range("G" & x).Value <> "" Then
        Rows(x + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("A" & x + 1 & ":D" & x + 1).Value = Range("A" & x & ":D" & x).Value
        Range("E" & x + 1).Value = Range("G" & x).Value
        Range("G" & x).Value = ""
    End If

    If Range("F" & x).Value <> "" Then
        Rows(x + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("A" & x + 1 & ":D" & x + 1).Value = Range("A" & x & ":D" & x).Value
        Range("E" & x + 1).Value = Range("F" & x).Value
        Range("F" & x).Value = ""
    End If

Next x

End Sub

answered Jan 12, 2017 at 23:22

VBA Pete

2,6762 gold badges26 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gary Over a year ago

Thank you. This was very helpful. Appreciate it. I added code to delete the columns F and G so that there are no headers left when code is run.

VBA Pete Over a year ago

@Gary No worries, glad to help. You can also just do do Range("F1:G1").Value = "" to clear the headers.

Collectives™ on Stack Overflow

Excel VBA - Split one row data in mutiple rows

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related