VBA Convert Range to into explizit values

[2leet4this]

Hello,

apparently I can't find the correct search string to solve my problem (I am completly new to VBA).

I got the following spreadsheet and want to convert the ranges to single values without copy/paste hundrets of values.

This is how my spreadsheet looks like:

Value1	Range from	Range to	Value 2
XX	100	1897	2
XX	2001	3078	2
YY	200	789	2

I need one looking like this:

Value1	Range from	Range to	Value 2
XX	100	100	2
XX	101	101	2
XX	102	102	2

and so on ...

Can you help me? Thank you for your support .

Best regards, 2leet4this

 

[Peter_SSs]

Welcome to the MrExcel board!

See if this does what you want.

I have assumed ..
- The original data is in columns A:D of the active sheet, starting in cell A1.
- Columns H:K of that same sheet are available to house the results (we can worry about putting them on another sheet later once we are sure the results are what you want).

Test in a copy of your workbook.

Sub Test()
  Dim a As Variant
  Dim i As Long, nr As Long, rws As Long
  
  a = Range("A2", Range("D" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    nr = Range("H" & Rows.Count).End(xlUp).Row + 1
    rws = a(i, 3) - a(i, 2) + 1
    Range("H" & nr).Resize(, 4).Value = Application.Index(a, i, 0)
    Range("J" & nr).Value = a(i, 2)
    Range("H" & nr).Resize(rws).Value = a(i, 1)
    Range("K" & nr).Resize(rws).Value = a(i, 4)
  Next i
  With Range("H2").CurrentRegion
    .SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]+1"
    .Value = .Value
  End With
End Sub

 

[2leet4this]

Hello Peter,

works like a charm! Thank you very much .

No need for further improvement, I can do the rest manually.

Best regards

2leet4this

 

[Peter_SSs]

Hello Peter,

works like a charm! Thank you very much .

No need for further improvement, I can do the rest manually.

Best regards

2leet4this

Cheers, glad to help.