How to display all numbers within a range

[pheens]

I've got an issue at work. I'm currently manually updating one spreadsheet from another. I've got a base of data that is all rangese in two columns with a third column of the total numbers within that range. For instance:
30000 30007 8

I've been taking that info and inserting the numbers within that range into another excel sheet like so:
30000
30001
30002
etc.
30007

I'd like to somehow be able to have excel display the numbers within the ranges automatically. I'm also inserting a break in the second spreadsheet between the high number of one range and the low of the next beginning range, I can either continue to do that or get rid of it, though it is helpful. Example below:

30007
*****
30023
30024
*****
30037
etc.

Any idea how to get these ranges/arrays broken back out? I did some searching for displaying all values, reversing a range/array but it always comes back with showing certain values, I want them all in some sort of spreadsheet. The format can change but I need the info.

THANKS!

 

[Sathish KV]

Hi,

Could you upload a sample file (both input and output files)

 

[pheens]

Sure, see attached.

The "unused" file is used to feed the "available" file.

 

[Somendra Misra]

Hi pheens,

Try below code on your sample file "ex Chronological All Unused" file.

Sub extractNumber()

Dim lr As Long, i As Long
Dim ws As Worksheet

Set ws = ThisWorkbook.ActiveSheet

With ws
    lr = .Cells(Rows.Count, "A").End(xlUp).Row
End With
k = 2
For i = 2 To lr

        For d = ws.Cells(i, 1).Value To ws.Cells(i, 2).Value
          ws.Cells(k, 5) = d
            k = k + 1
        Next d
        ws.Cells(k, 5) = "******************"
        k = k + 1
Next i


End Sub

Regards,

 

[shrivallabha]

Here's one more code for the same workbook:

Public Sub ProcessData()
Dim lngCount As Long, BasVal As Long, TopVal As Long
lngCount = 2
Application.ScreenUpdating = False
Columns("D:D").Value = ""
Do While Len(Cells(lngCount, "A")) > 0
  BasVal = Cells(lngCount, "A").Value
  TopVal = Cells(lngCount, "B").Value + 1
  Do While BasVal < TopVal
  Cells(Rows.Count, "D").End(xlUp).Offset(1, 0).Value = BasVal
  BasVal = BasVal + 1
  Loop
  Cells(Rows.Count, "D").End(xlUp).Offset(1, 0).Value = "*****"
  lngCount = lngCount + 1
Loop
Application.ScreenUpdating = True
End Sub

 

[pheens]

Guess I should figure out how to use Macros, eh? I'll do some searching.

 

[shrivallabha]

1. Press ALT+F11 to open Visual Basic Editor Window.
2. Choose Insert | Modules
3. Paste Codes there and return to Excel.
4. Press ALT+F8 to bring up Run Macro dialog.
5. Select appropriate macro and press run.

Here's code for the other requirement.

Public Sub AddSeparator()
Dim lngRow As Long
Application.ScreenUpdating = False
lngRow = Cells(Rows.Count, "A").End(xlUp).Row
Do While lngRow >= 2
  If Cells(lngRow, "A").Value <> Cells(lngRow - 1, "A").Value + 1 Then
  Cells(lngRow, "A").Resize(1, 5).Insert xlDown
  Cells(lngRow, "A").Resize(1, 5).Value = Array("****", "****", "****", "****", "****")
  End If
  lngRow = lngRow - 1
Loop
Application.ScreenUpdating = True
End Sub

 

[pheens]

I used the first code with sufficient success. I clicked view code in excel, pasted the code, hit f5, and there the numbers were. I really appreciate it. I like excel and use it frequently, but I'm just starting to get into pivot tables, and now perhaps coding. There's so much that can be accomplished...

 

[toguko]

NIce codes folks...