Expanding a Range of Numbers using VBA

[LeoDrummond]

Hello,

I have a long list of numbers- see example

279.49
290.0
260-264.99
338-338.99
v27.9
v25.41

I am having difficulty creating a VBA script that can recognize where a dash is present, and then expand those numbers as follows to become

279.49
290.0
260
261
262
263
264
338
v27.9
v25.41

I need the numbers to expand within the same column, and all numbers need to round down to the nearest 1. Also, it would be great if all other values on the same row as "640-649.99" could be replicated for each new row that is created with the expanding of that range. Oh, and notice that some numbers include a "v" at the beginning.

Thank you very much

 

[Rick Rothstein]

Oh, and notice that some numbers include a "v" at the beginning.

Some questions for clarification...

1) So, can there be dashes with your "v" numbers?

2) If so, will both "numbers" have the "v" in front of them or only the first one?

3) Could there be any other letter beside "v"?

4) Will the "v" (or any other letter if the answer to Question #3 was "yes") always be lower case as shown?

 

[LeoDrummond]

Some questions for clarification...

1) So, can there be dashes with your "v" numbers? the "v" numbers will never have dashes. The "v" numbers will never include a range and will never need to be expanded.

2) If so, will both "numbers" have the "v" in front of them or only the first one?

3) Could there be any other letter beside "v"? only "v"

4) Will the "v" (or any other letter if the answer to Question #3 was "yes") always be lower case as shown? the "v" will always be lower case

 

[Rick Rothstein]

Give this macro a try...

Sub ExpandRanges()
  Dim R As Long, N As Long, LastRow As Long, LastCol, LeftRight As Variant
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
  Application.ScreenUpdating = False
  For R = LastRow To 1 Step -1
    If InStr(Cells(R, "A").Value, "-") Then
      LeftRight = Split(Cells(R, "A").Value, "-")
      If Int(LeftRight(0)) <> Int(LeftRight(1)) Then
        Cells(R + 1, "A").Resize(Int(LeftRight(1)) - Int(LeftRight(0))).EntireRow.Insert
        For N = 0 To Int(LeftRight(1)) - Int(LeftRight(0))
          Cells(R + N, "A") = Int(LeftRight(0)) + N
        Next
      Else
        Cells(R, "A").Value = Int(LeftRight(0))
      End If
    End If
  Next
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  With Intersect(Rows("2:" & LastRow), Columns("B").Resize(, LastCol - 1))
    .SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
  End With
  Application.ScreenUpdating = True
End Sub