This forum uses cookies
This forum makes use of cookies to store your login information if you are registered, and your last visit if you are not. Cookies are small text documents stored on your computer; the cookies set by this forum can only be used on this website and pose no security risk. Cookies on this forum also track the specific topics you have read and when you last read them. Please confirm whether you accept or reject these cookies being set.

A cookie will be stored in your browser regardless of choice to prevent you being asked this question again. You will be able to change your cookie settings at any time using the link in the footer.


Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Excel: Expanding a range of numbers
#1
Having a technical glitch.

How in Excel can I expand a range of numbers to show every number in that range? For exampe in cell A1 I have the number 1 and Cell B1 I have 100. What function can I use to make excel display the values 1,2,3,4,5,6,7,8 . . . . 98, 99,100 in a coloumb.

I have lots of long ranges with lots of sub ranges so merely dragging numbers down the page isn't practical and even worse would be typing them manually. I'm thinking I may have the resort to VBA which isn't my strongest point.
Reply
#2
"General Sparks" Wrote:Having a technical glitch.

How in Excel can I expand a range of numbers to show every number in that range? For exampe in cell A1 I have the number 1 and Cell B1 I have 100. What function can I use to make excel display the values 1,2,3,4,5,6,7,8 . . . . 98, 99,100 in a coloumb.

I have lots of long ranges with lots of sub ranges so merely dragging numbers down the page isn't practical and even worse would be typing them manually. I'm thinking I may have the resort to VBA which isn't my strongest point.

If you're just setting something up use a formula like in B1: =A1+1, copy the formula to all the affected cells, then select all, copy, paste special, values... and you have it done.
Reply
#3
in the cell with '1' hover over bottom right corner and auto fill, fill series drag until desired number is reached...that's on 2010 version
Reply
#4
I'm aware I can drag down, whether that be Mudmonkeys formula or the icon in the bottom corner of a cell, but I've hundreds of ranges with hundreds of numbers in each, dragging down for each isn't practical and too time consuming.

Cheers but need something slicker.
Reply
#5
I've left it a bit late but....

Code:
Function doSomething(myFrom As Long, myTo As Long) As String
    For a = myFrom To myTo
        doSomething = doSomething & IIf(doSomething = "", "", ",") & a
    Next a
End Function

You will need to use this in a module to make it work.

Have fun
Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)