CODEWITHSUNDEEP
vbaexcel
Gary
Gary

Reputation: 13

Excel VBA - Split one row data in mutiple rows

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.

Upvotes: 1

Views: 427

Answers (1)

VBA Pete
VBA Pete

Reputation: 2666

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

Upvotes: 1

Related Questions