Convert Data Range in to individual values

M

mistryrg

Hi,

Need some help!

I am trying to convert a spreadsheet of data which has individual cell
and data-range cells and convert them into indvidual values.

e.g.

50010
50013
50020-50033
50050

The individual cells are fine, but the 50020-50033 for example I wan
to be able to convert/export as individual cells, i.e. 50020, 50021
50022,...50031,50033.

Can anyone please help me with this.

All help is very much appreciated

Thanks
Raj Mistr
 
D

Don Guillett

If you meant
50020
50021
50022
50023
50024
50025

then try

Sub fixdata()
For i = Cells(Rows.Count, "d").End(xlUp).Row To 2 Step -1
Set mc = Cells(i, 4)
inst = InStr(mc, "-")
If inst > 0 Then
n1 = Left(mc, inst - 1)
n2 = Right(mc, Len(mc) - inst)
Count = n2 - n1
mc.Value = n1
Cells(mc.Row + 1, 4).Resize(Count, 1).EntireRow.Insert
mc.AutoFill Destination:=mc.Resize(Count + 1)
End If
Next i
End Sub
 
Top