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:

[table="width: 500"]
[tr]
[td]Value1[/td]
[td]Range from[/td]
[td]Range to[/td]
[td]Value 2[/td]
[/tr]
[tr]
[td]XX[/td]
[td]100[/td]
[td]1897[/td]
[td]2[/td]
[/tr]
[tr]
[td]XX[/td]
[td]2001[/td]
[td]3078[/td]
[td]2[/td]
[/tr]
[tr]
[td]YY[/td]
[td]200[/td]
[td]789[/td]
[td]2[/td]
[/tr]
[/table]

I need one looking like this:

[table="width: 500"]
[tr]
[td]Value1[/td]
[td]Range from[/td]
[td]Range to[/td]
[td]Value 2[/td]
[/tr]
[tr]
[td]XX[/td]
[td]100[/td]
[td]100[/td]
[td]2[/td]
[/tr]
[tr]
[td]XX[/td]
[td]101[/td]
[td]101[/td]
[td]2[/td]
[/tr]
[tr]
[td]XX[/td]
[td]102[/td]
[td]102[/td]
[td]2[/td]
[/tr]
[/table]

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.