Breaking up a Range in 2 Cells with Variable number length.

[Bytoro]

Hello,
I am trying to create a VB button with the below output. I would like to break up the range of numbers located in 2 cells with the name of 1 for each. As you can see, some might not be a range just 2 of the same numbers, indicating just one product id. 1 digit is the minimum and 9 digits is the max for each product id.


With the help of some Mr. Excel Dueling videos, i was able to make it work for a range with exact character counts but for a small protion of the data it is not flexbible enough to handle smaller ranges.


Thank you for any assistance! This is a great resource and community!

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Name	NU	NU	NU	NU	NU	Product ID Start	Product ID END								Name	Individual ID
2	Item 1	x	x	x	x	x	101035499	101035499								Item 1	101035499
3	Item 1	x	x	x	x	x	101026465	101026478								Item 1	101026465
4	Item 2	x	x	x	x	x	1004	1006								Item 1	101026466
5	Item 3	x	x	x	x	x	55023	55023								Item 1	101026467
6	Item 3	x	x	x	x	x	54526	54828								Item 1	101026468
7	Item 3	x	x	x	x	x	101026031	101026032								Item 2	1004
8																Item 2	1005
9																Item 2	1006
10																Item 3	55023
11																Item 3	54526
12																Item 3	101026031
13																Item 3	101026032
Sheet1

 

[Legacy 143009]

Like this?

Sub test()
  Dim length As Long, myArr As Variant, tmp As Variant, lRow As Long
  lRow = Cells(Rows.Count, 7).End(xlUp).Row
  tmp = Range("A2:H" & lRow)
  For i = 1 To UBound(tmp)
    length = length + tmp(i, 8) - tmp(i, 7) + 1
  Next
  ReDim myArr(1 To length, 1 To 2)
  lRow = 1
  For i = 1 To UBound(tmp)
    For j = 1 To tmp(i, 8) - tmp(i, 7) + 1
      myArr(lRow, 1) = tmp(i, 1)
      myArr(lRow, 2) = tmp(i, 7) + (j - 1)
      lRow = lRow + 1
    Next
  Next
  Range("P2").Resize(length, 2) = myArr
End Sub

 

[Bytoro]

Wow such a fast response! Thanks! The data set it about 6000 records and when i run it for the complete data set, i get "out of memory." I ran it for 1500 of them and it worked great. I got excited!. I am trying to see if this is a record number limitation or something with the data set. i can keep you posted if i find anything.
As you know, it loops all 6000 records for the first "for" statement records. I may be able to edit the range and just create 5 buttons. One for Each range.

I have done all the obvious things, closed programs, restarted. verified in task manager that i have sufficient memory. 32gb ram. i couldnt determine if cache needed to be cleared.

 

[Legacy 143009]

Maybe..

Also there is a probabilty that the number of inserted rows may exceed the total number of rows in excel. It would be better to process in batches.

 

[Bytoro]

I found a few errors in the data set and was able to run the full thing. One had a range of about 9 million. Thanks so much, this a big win for my day to day! Calling it solved.

 

[Legacy 143009]

I am glad it did help! Thaks for the feedback

 

[Legacy 143009]

This may recude the calculation time insignificantly

    For j = 0 To tmp(i, 8) - tmp(i, 7)
      myArr(lRow, 1) = tmp(i, 1)
      myArr(lRow, 2) = tmp(i, 7) + j
      lRow = lRow + 1
    Next