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

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

Select allOpen in new window