Avatar of Alex Campbell
Alex Campbell
Flag for United States of America 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
RVBAMicrosoft ExcelStatistical PackagesMicrosoft Office

Avatar of undefined
Last Comment
Alex Campbell

8/22/2022 - Mon
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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER CERTIFIED SOLUTION
Bill Prew

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Alex Campbell

ASKER
Great!! Thanks!