Split ranges(001-005) but keep the associated column

[norriz]

I have table with a range of numbers with the associated name(below), need to find a way to split ex:001-005 to 001 and table1, 002 and table1, 003 and table1. Can this be done in access or excel. Help needed urgently. Thank you

Number	Name
001-005	table1
006-010	table2

I'd like my final data to look like this-
Number Name
001 Table1
002 Table1

 

[kweaver]

Who's Paul.

Peter, I meant...not enough coffee yet.

 

[Peter_SSs]

Can I put this in access as well.

OK, I have re-opened your duplicate question in the Access Forum, creating a cross-post here: Split Number Ranges(001-055) from a Group

 

[Peter_SSs]

That's a great approach, PaulPeter...but I got an error on the line just above "End Sub"

I copied your mini-sheet and ran the code without error so I'm not sure why it doesn't work for you.
I'm guessing at the problem line k=0 but I don't know why. If you step through the code with F8 I'm assuming that it never executes the two lines in the For j ... Next j loop?

 

[kweaver]

Right about never executing the For j loop.

I used this small data sample.

MergeCells05202021.xlsm
	A	B
1	Range	Number
2	002-009	5
3	007-010, 555	6
4	143	7
5	501-512	8
6	600-606	8
SheetA

 

[Peter_SSs]

Right about never executing the For j loop.

So when you step past the line below for the first time, what value is held by the variable "itm"?

For Each itm In Split(Replace(a(i, 1), " ", ""), ",")

I used this small data sample.

What does that sample have to do with this thread?

 

[kweaver]

So when you step past the line below for the first time, what value is held by the variable "itm"?

For Each itm In Split(Replace(a(i, 1), " ", ""), ",")

What does that sample have to do with this thread?

I was working on another project and the macro was stopped so the clipboard had the incorrect copy. I correct it but you were too fast on the viewing. LOL.

 

[kweaver]

So when you step past the line below for the first time, what value is held by the variable "itm"?

For Each itm In Split(Replace(a(i, 1), " ", ""), ",")

What does that sample have to do with this thread?

itm is empty