Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.
Daily Dose of Excel » All Tags » Arrays (RSS)

Browse by Tags

  • Getting Array Data from a Filtered List in VBA

    Getting data from an Excel sheet into an array is usually best accomplished with a statement that looks something like this: vMyArray = Sheet1.UsedRange.Value I thought it would be keen to fill an array from a filtered list, so I coded Sub ArrFilteredList()         Dim vArr As Variant         vArr = Sheet1.UsedRange.SpecialCells(xlCellTypeVisible).Value       [...] Read More...
    Filed under:

  • Multiple Substitute VBA

    In regard to Multiple Substitute Formula, here’s one way to do it in VBA. Sub RemoveStates()         Dim rInput As Range     Dim rStates As Range     Dim vaInput As Variant     Dim vaStates As Variant     Dim i As Long, j As Long     Dim sTemp As String         Set rInput [...] Read More...
    Filed under:

  • A quick tip - Working with arrays with unknown bounds

    Two recent instances required transferring data from one array to another. One was a solution to speed up the use of COUNTIF for each element in a large range testing against another large range. In the process I had to build several building blocks including a replacement for the native Excel TRANSPOSE function, [...] Read More...
    Filed under: , ,

  • VBA Random Integers

    Every time I post code that includes picking a random integer, Tushar reminds me that my methods produce biased results. The last time, I swore to myself I would do it right. Here's how the old me would have done it: Sub RandTest1()         Dim lRand As Long     Dim i As [...] Read More...
    Filed under:

  • My own Euler problem

    Hi everyone, With all these Euler posts I thought, why not post my own? Suppose the following: I have a products table set up like this: Code ProdName Price 0001 Product1 556.68 0002 Product2 977.41 0003 Product3 350.62 0004 Product4 509.16 0005 Product5 748.4 0006 Product6 802.96 (list goes on to as much as 5000 products) And I want to show this table in a userform and give the user some filtering possibilities (lets say on the first two [...] Read More...

  • Arrays Explained

    Here's a nice explanation of arrays Arrays, What's the Point On the Stackoverlow Podcast, Joel and Jeff agree that arrays are obsolete. There are far better data structures, most of which are built on arrays. I still use arrays quite a bit. But considering I'm using an obsolete language (VBA = VB6), I guess that's not [...] Read More...
    Filed under:

  • Efficient Looping

    In a previous post, I demonstrated how to use constants to improve your code. Then everyone started beating up my loop. The code wasn't solving a real life problem, so I just threw any old loop together. It wasn't relevant because that's not what the post was about. To fight back, [...] Read More...
    Filed under:
Copyright Excel User Group and the relevant contributors, 2009. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.