List/Fill Numbers Between Two Values

[Closed]
Report
Posts
12
Registration date
Wednesday September 19, 2012
Status
Member
Last seen
January 5, 2013
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,



I have list of numbers...let's say 1 to 10
25 to 30
50 to 54

I would like to fill in the middle numbers to lst in Excel as 1
2
3
4
5
6
7
8
9
10
25
26
27
28
29
30
50
51
52
53
54



but my data is so huge it is almost about 30 lacs. So,if there is any way to make autp fillup of spredsheet ...that will be great. Please help in the issue

4 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
when you say you have list of number, where is this list ? in what format etc
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Posts
12
Registration date
Wednesday September 19, 2012
Status
Member
Last seen
January 5, 2013

Start Serail End Serail
206123646776 206123699999
206123710000 206123799999
206123810000 206123899999
206123910000 206123999999
107123716300 107123799999
107124131231 107124199999
107124210000 107124299999
107124310000 107124399999
107124410000 107124499999
107124510000 107124599999
107124610000 107124669999
108124410000 108124499999
108124510000 108124599999
108124610000 108124699999
108124710000 108124799999
108124810000 108124899999
108124910000 108124999999
108125010000 108125099999
108125110000 108125199999
108125210000 108125299999
108125310000 108125399999
108125410000 108125499999
108125510000 108125599999
108125610000 108125699999
108125710000 108125799999
108125810000 108125899999
108125910000 108125999999
108126010000 108126099999
108126110000 108126199999
108126210000 108126299999

Format is this . I require the data in excelsheet 2.in one shot.
Posts
12
Registration date
Wednesday September 19, 2012
Status
Member
Last seen
January 5, 2013

Start Serail End Serail
206123646776 206123699999
206123710000 206123799999
206123810000 206123899999
206123910000 206123999999
107123716300 107123799999
107124131231 107124199999
107124210000 107124299999
107124310000 107124399999
107124410000 107124499999
107124510000 107124599999
107124610000 107124669999
108124410000 108124499999
108124510000 108124599999
108124610000 108124699999
108124710000 108124799999
108124810000 108124899999
108124910000 108124999999
108125010000 108125099999
108125110000 108125199999
108125210000 108125299999
108125310000 108125399999
108125410000 108125499999
108125510000 108125599999
108125610000 108125699999
108125710000 108125799999
108125810000 108125899999
108125910000 108125999999
108126010000 108126099999
108126110000 108126199999
108126210000 108126299999

This is the data in excel sheet1.I require the output in sheet2 continously in one shot.Please assist
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
You have more rows than excel can take

if you fix that issue, then you can use this macro

public Sub FillInNumbers()
    
    Dim sourceSheetName                 As String
    Dim desitinationSheetName           As String
    
    Dim startPoint                      As Double
    Dim endPoint                        As Double
    Dim maximumSourceRow                As Long
    Dim processRow                      As Long
    Dim sourceRow                       As Long
    Dim deltaRow                        As Double
    
    sourceSheetName = "Sheet1"
    desitinationSheetName = "Sheet2"
    
    With Sheets(sourceSheetName)
        maximumSourceRow = getItemLocation("*", .Range(.Cells(1, "A"), .Cells(.Rows.Count, "B")))
        If (maximumSourceRow < 2) Then Exit Sub
    End With
    
    With Sheets(desitinationSheetName)
        .Columns(1).Clear
        .Columns(1).NumberFormat = "#"
        processRow = 0
        For sourceRow = 2 To maximumSourceRow
            startPoint = Sheets(sourceSheetName).Cells(sourceRow, "A")
            endPoint = Sheets(sourceSheetName).Cells(sourceRow, "B")
            deltaRow = endPoint - startPoint
            processRow = processRow + 1
            .Cells(processRow, "A").Value = startPoint
            .Cells(processRow, "A").AutoFill Destination:=.Range(.Cells(processRow, "A"), .Cells(processRow + deltaRow, "A")), Type:=xlFillSeries
            processRow = processRow + deltaRow
        Next sourceRow
    End With
    
    
    
End Sub

Public Function getItemLocation(sLookFor As String, _
                                rngSearch As Range, _
                                Optional bFullString As Boolean = True, _
                                Optional bLastOccurance As Boolean = True, _
                                Optional bFindRow As Boolean = True) As Long
                                   
   'find the first/last row/column  within a range for a specific string
      
   Dim Cell             As Range
   Dim iLookAt          As Integer
   Dim iSearchDir       As Integer
   Dim iSearchOdr       As Integer
         
   If (bFullString) _
   Then
      iLookAt = xlWhole
   Else
      iLookAt = xlPart
   End If
   If (bLastOccurance) _
   Then
      iSearchDir = xlPrevious
   Else
      iSearchDir = xlNext
   End If
   If Not (bFindRow) _
   Then
      iSearchOdr = xlByColumns
   Else
      iSearchOdr = xlByRows
   End If
         
   With rngSearch
      If (bLastOccurance) _
      Then
         Set Cell = .Find(sLookFor, .Cells(1, 1), xlValues, iLookAt, iSearchOdr, iSearchDir)
      Else
         Set Cell = .Find(sLookFor, .Cells(.Rows.Count, .Columns.Count), xlValues, iLookAt, iSearchOdr, iSearchDir)
      End If
   End With
         
   If Cell Is Nothing Then
      getItemLocation = 0
   ElseIf Not (bFindRow) _
   Then
      getItemLocation = Cell.Column
   Else
      getItemLocation = Cell.Row
   End If
   Set Cell = Nothing

End Function
Posts
12
Registration date
Wednesday September 19, 2012
Status
Member
Last seen
January 5, 2013

Thnx ...It is working fine.
But there is an issue with the data capacity.

Is there any way to make automatic excel sheets like sheet3,sheet4,sheet5 etc. when it excedds the data capacity limits (as xlsx accepts upto 10 lacs)
please help
Posts
12
Registration date
Wednesday September 19, 2012
Status
Member
Last seen
January 5, 2013

Can this macro be used in MSaccess ? Because MSACCESS has much more data capacity than excel.I will assign the macro in a button .
Posts
12
Registration date
Wednesday September 19, 2012
Status
Member
Last seen
January 5, 2013

Thnx its working fine..but there is an issue with the data capacity.

Is there any way to make automatic sheets like sheet1,sheet2,sheet3.when it completes the data capacity limit (example for xlsx accepts 1048576.So when the limit exceeds it should automatically go to the next sheet)
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
with amount of data that you have. access would be better choice.
Posts
12
Registration date
Wednesday September 19, 2012
Status
Member
Last seen
January 5, 2013

But how can I implement this formula in msaccess.Please assit
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
well one idea could be that in one table you enter these ranges. Then you can write a small script to add new records in a new table based on these ranges. a lot depends on usage