PDA

View Full Version : [SOLVED] How to get the first & last range value for this type of data...?



bmba007
11-30-2019, 10:12 PM
In one of my excel files, in a column, there are some numeric values e.g.



VL. No.


2


1


5


3


4


8


11


1


3



In another sheet of that file I want to write the starting range and ending range like this using the data from the above column using VBA:




First NumRange
Last NumRange


1/38
2/38


3/38
3/38


4/38
8/38


9/38
11/38


12/38
15/38


16/38
23/38


24/38
34/38


35/38
35/38


36/38
38/38



The 38 is the sum of all the values from column VL. No. How can I achieve this using VBA?

Thanks:)

Bob Phillips
12-01-2019, 04:13 AM
Public Sub ListRanges()
Dim lastrow As Long
Dim startnum As Long, endnum As Long, total As Long
Dim i As Long

Application.ScreenUpdating = False

With ActiveSheet

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
startnum = 1: endnum = 0
total = Application.Sum(.Range("A2").Resize(lastrow - 1))

.Range("C1:D1").Value = Array("First NumRange", "Last NumRange")
.Range("C1:D1").WrapText = True
For i = 2 To lastrow

endnum = endnum + .Cells(i, "A").Value
.Cells(i, "C").Value = "'" & startnum & "/" & total
.Cells(i, "D").Value = "'" & endnum & "/" & total

startnum = endnum + 1
Next i
End With

Application.ScreenUpdating = True
End Sub