Start & End Number Further Converted

[Rick Rothstein]

Give the following macro a try and see if you can make use of it...

Sub StartEndRanges()
  Dim X As Long, TargetNumber As Long, NumberOfRows As Long, LastRow As Long, Increment As Long, DestinationStartCell As Range
  Const FixedQty As Long = 400
  Const StartRow As Long = 2
  TargetNumber = Application.InputBox("What quantity total do you want ranges for (note... must be a multiple of 400)?", Type:=1)
  If TargetNumber = 0 Or TargetNumber Mod 400 <> 0 Then
    MsgBox "The number """ & TargetNumber & """ is not valid!", vbExclamation
  End If
  On Error GoTo NoCell
  Set DestinationStartCell = Application.InputBox("Please select the start cell for output?", Type:=8)
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  NumberOfRows = TargetNumber \ FixedQty
  With DestinationStartCell
    .Resize(, 3).Merge
    .Value = "Result After Macro: " & TargetNumber & " or less"
    .HorizontalAlignment = xlHAlignCenter
    .Interior.ColorIndex = 48
    .Font.Bold = True
    With .Offset(1).Resize(, 3)
      .Value = Array("Start Range", "End Range", "Qty")
      .Interior.ColorIndex = 15
      .HorizontalAlignment = xlHAlignCenter
      .Font.Bold = True
      .ColumnWidth = 15
    End With
  End With
  For X = StartRow To LastRow Step NumberOfRows
    DestinationStartCell.Offset(2 + Increment).Value = Cells(X, "A").Value
    DestinationStartCell.Offset(2 + Increment).Offset(, 1).Value = Cells(X + NumberOfRows - 1, "B").Value
    DestinationStartCell.Offset(2 + Increment).Offset(, 2).Value = WorksheetFunction.Sum(Cells(X, "C").Resize(NumberOfRows))
    Increment = Increment + 1
  Next
  DestinationStartCell.Offset(1 + Increment).Offset(, 1).Value = Cells(LastRow, "B").Value
NoCell:
End Sub

Note: The code will ask you for the quantity total you want the list broken down by (you would 20000 for your first chart, 100000 for your second chart) and the starting cell (top left corner) to output the chart to (which does not have to be on Row 1 if you do not want it to be).

[ayazgreat]

Thank you very Rick Rothstein
It works really great , more than my expectation.

[Rick Rothstein]

Thank you very Rick Rothstein
It works really great , more than my expectation.

You are quite welcome... I am glad I was able to be of help to you.

[ayazgreat]

Rick Rothstein
I want to ask something more regarding above post that if Column a & b Start & End number but Column C Qty of each range is 20000 and I want to convert each range from col a to c same into 400 as in previouse code we have qty in col c and we can convert them to 20000 or more but Now case is oposite it.

Thanks in advance