List alpha numeric numbers between two references

[sedwardson]

I found this by Snakehips and it was VERY helpful ! I tried to modify it but found that it only works with numeric entries and not alpha numeric entries.

Rich (BB code):

Sub Fill()    [A4] = [A1].Value
    ato = [B1].Value
    [B4] = [A2].Value
    bto = [B2].Value
    [C4] = [A3].Value
    cto = [B3].Value
    [A4].DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1, Stop:=ato
    [B4].DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1, Stop:=bto
[C4].DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1, Stop:=cto
End Sub

The code I wrote (but failed to get to work) was:

Sub Fill()    
    [B3] = [B4].Value
    bto = [C3].Value
    [B4].DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1, Stop:=bto
End Sub

This works with numbers but not for the range I have which is: 531PZ228173 - 531PZ228222

Can anyone help with how I might get the range to work with this cell entry. Thanks.
P.S. Sorry if I've missed some detail out or you require more from me. This is my first post ! Please bear with me

 

[Legacy 143009]

Please find the sample file. I hope it will make things more clear:

Sample.xlsm

1 file sent via WeTransfer, the simplest way to send your files around the world

we.tl

 

[Legacy 143009]

ok, got it !

Nice Glad for it did work!

 

[sedwardson]

Thanks so much for this. I will have some more ranges where I wont know (or there will be a larger amount in the range) is there a way of expanding the range until it gets to an ending number?

 

[Legacy 143009]

I have to see the sample data.

 

[sedwardson]

I have to see the sample data.

These will be the ranges I am looking to expand out:

Thanks

 

[Legacy 143009]

Oh I got it! But I have to insert 48 new rows between B4 and B5. Is this what you want?

 

[Legacy 143009]

I assume you can use this code:

Sub test()
  Dim lRow As Long, r As Long
  lRow = Cells(Rows.Count, 2).End(xlUp).Row
  Application.ScreenUpdating = False
  For i = lRow To 4 Step -1
    r = Right(Cells(i, 3).Value, 6) - Right(Cells(i, 2).Value, 6)
    Rows(i + 1).Resize(r).EntireRow.Insert
    With Range("B" & i)
    .AutoFill Destination:=.Resize(r + 1)
    End With
  Next
  Application.ScreenUpdating = True
End Sub

But you have large gaps between your numbers. Excel may not have as many rows as you need.

 

[sedwardson]

Oh I got it! But I have to insert 48 new rows between B4 and B5. Is this what you want?

I can have a different macro for each column if it helps....

 

[sedwardson]

The numbers are premium bond allocation numbers. I want to expand the ranges so I can see who has won when the draw takes place. For example, if number 528CY422556 is drawn (or a number in the range

528CY422556 - 528CY422605

) I will know who has won. Does that make sense?

 

[Legacy 143009]

have you tried the code on post #17