asked on

How to Tell Macro to Create New Rows Based on a Particular Column Rather Than Hard Coded Column G

I had this question after viewing How to change this macro to find last column instead of A to J?.

Sub Commas2Rows()
' hiker95, 05/18/2017, ME1006027
Dim lr As Long, lc As Long, r As Long, s, i As Long
Application.ScreenUpdating = False
With ActiveSheet
lr = .Cells(Rows.Count, 1).End(xlUp).Row
lc = .Cells(2, Columns.Count).End(xlToLeft).Column
For r = lr To 2 Step -1
If InStr(.Range("G" & r), ", ") Then
s = Split(.Range("G" & r), ", ")
.Rows(r + 1).Resize(UBound(s)).Insert
.Range("G" & r).Resize(UBound(s) + 1) = Application.Transpose(s)
.Range("A" & r + 1 & ":F" & r + 1).Resize(UBound(s)).Value = .Range("A" & r & ":F" & r).Value
' .Range("H" & r + 1 & ":J" & r + 1).Resize(UBound(s)).Value = .Range("H" & r & ":J" & r).Value
.Range("H" & r + 1 & ":" & Chr(64 + lc) & r + 1).Resize(UBound(s)).Value = .Range("H" & r & ":" & Chr(64 + lc) & r).Value
End If
Next r
End With
Application.ScreenUpdating = True
End Sub

Bill Prew

I think this should handle that.

Sub Commas2Rows()
  ' hiker95, 05/18/2017, ME1006027
  Dim lr As Long, lc As Long, r As Long, s, i As Long, SplitCol As String, SplitBefore As String, SplitAfter As String

  SplitCol = "G"
  SplitBefore =  Chr(Asc(SplitCol)-1)
  SplitAfter = Chr(Asc(SplitCol)+1)

  Application.ScreenUpdating = False

  With ActiveSheet
    lr = .Cells(Rows.Count, 1).End(xlUp).Row
    lc = .Cells(2, Columns.Count).End(xlToLeft).Column

    For r = lr To 2 Step -1
      If InStr(.Range(SplitCol & r), ", ") Then
        s = Split(.Range(SplitCol & r), ", ")
        .Rows(r + 1).Resize(UBound(s)).Insert
        .Range(SplitCol & r).Resize(UBound(s) + 1) = Application.Transpose(s)
        .Range("A" & r + 1 & ":" & SplitBefore & r + 1).Resize(UBound(s)).Value = .Range("A" & r & ":" & SplitBefore & r).Value
        .Range(SplitAfter & r + 1 & ":" & Chr(64 + lc) & r + 1).Resize(UBound(s)).Value = .Range(SplitAfter & r & ":" & Chr(64 + lc) & r).Value
      End If
    Next r
  End With

  Application.ScreenUpdating = True
End Sub

Open in new window

»bp

Bill Prew

And if it were me I would probably switch from .Range() referencing to .Cells() referencing for this, to avoid the concatenations and character conversions, etc. Would be a bit simpler and easier to follow I suspect.

»bp

Alex Campbell

ASKER

That works, but it would be great if you would stop and ask which column to work with it you would.

ASKER CERTIFIED SOLUTION

Bill Prew

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Alex Campbell

ASKER

Great!! Thanks!