423,682 Members | 1,327 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,682 IT Pros & Developers. It's quick & easy.

Revise Code to Append Expanded Numbers Properly

P: 4
So I'm having an issue with my current code.

Expand|Select|Wrap|Line Numbers
  1.  Sub ExpandRanges()
  2.  
  3. Dim X As Long, CG As Variant, Rng As Range, Cell As Range
  4. Dim Series As String, CommaGroups() As String, DashGroups() As String
  5. Dim j As Long, lastrow As Long
  6.  
  7. j = 0
  8. lastrow = Cells(Rows.Count, "H").End(xlUp).Row
  9.  
  10. Set Rng = Range(Range("H2"), Range("H" & lastrow))
  11.  
  12. For Each Cell In Rng
  13.  
  14. CommaGroups = Split(Cell, ",")
  15.  
  16. For Each CG In CommaGroups
  17.  
  18.     DashGroups = Split(CG, "-")
  19.  
  20.     For X = DashGroups(0) To DashGroups(UBound(DashGroups))
  21.         If j = 0 Then j = Split(Cell.Address, "$")(2)
  22.  
  23.         Rows(j + 1 & ":" & j + 1).Insert Shift:=xlDown
  24.         Cells(j, 9).Value = X
  25.  
  26.         Range("A" & j + 1 & ":H" & j + 1).Value = Range("A" & j & ":H" & j).Value
  27.  
  28.         j = j + 1
  29.     Next
  30.  
  31. Next
  32.  
  33. Next
  34.  
  35. 'Band-aid solution
  36. lastrow = Cells(Rows.Count, "H").End(xlUp).Row
  37. Range("A" & lastrow & ":H" & lastrow).ClearContents
  38.  
  39. End Sub
  40.  
It currently is expanding the number range for only ONE range of numbers in my excel file, any time I try to add another range it will freeze and not respond.

For example, here is my input data for just one row:



When I run my code, this is my output which is exactly what I want:



But now the issue is, whenever I got to add another row of number ranges like this:



My excel pane will freeze and tell me it is not responding.

Any ideas on how to format the code to be able to append all the expanded ranges in new rows? Thanks!!
Attached Images
File Type: png Capture7 (2).PNG (6.5 KB, 26 views)
File Type: png Capture2.PNG (14.1 KB, 29 views)
File Type: png Capture7 (3).PNG (8.9 KB, 26 views)
Sep 10 '18 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.