Recovering and sorting data from a sparse worksheet in Excel VBA

Question

I have several sparsely populated tables in an Excel workbook from which I need to recover and sort the values for specific keys. The lookups will be triggered by Events.

I could do this using Loops and an array but having been warned off Loops in VBA I am wondering if there is a more elegant method perhaps using an inbuilt excel function.

Speed is important.

UID| UK | FR | DE | FI | LUX | .....
 1 |  0 |  0 |0.03| 0.1|   0 | .....
 2 |  0 |  0 |  0 |  0 | 0.5 | .....
 3 |0.01|  0 |  0 |  0 | 0.09| .....
 4 |  0 |  0 |  0 | 0.2|   0 | .....
 5 |0.31|0.07|  0 |  0 |   0 | .....
 .    .    .    .    .     .
 .    .    .    .    .     .
 .    .    .    .    .     .
 .    .    .    .    .     .

So in this sample table I would want to return the geographic distribution of each Unique ID and sort it, omitting any region with 0 weighting.

The table sheet has several thousand UIDs and several thousand regions.

He was warned off loops because repeatedly accessing a workbook from VBA e.g. through a loop is super slow. Nothing wrong with loops per se. — Jean-François Corbett
– Jean-François Corbett, Commented Mar 22, 2011 at 0:25

Jean-François Corbett · Accepted Answer · 2014-09-03 07:55:42Z

2

It's still not quite clear to me what you're trying to accomplish, but I will say this much: there is no built-in Excel function to do it -- or at least not as well as if you code it yourself.

Try this:

Dim rngData As Range
Dim varDummy As Variant
Dim iUID As Long, iRegion As Long

Set rngData = Range("A2:Z50") ' or wherever your data is

varDummy = rngData ' Reads in whole range at once into array. (Must be Variant.)
                   ' Much quicker than accessing one cell at a time.

' Go nuts with loops here.
For iUID = LBound(varDummy,1) To UBound(varDummy,1)
    For iRegion = LBound(varDummy,2) To UBound(varDummy,2)

        If varDummy(iUID,iRegion) = 0 Then
            ' Nothing here, ignore this cell.
            ' Do nothing.
        Else
            ' Do your thang
            ' ... code for whatever it is you want to do goes here.
        End If

    Next iRegion
Next iUID

edited Sep 3, 2014 at 7:55

answered Mar 22, 2011 at 0:41

Jean-François Corbett

38.7k30 gold badges145 silver badges192 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Recovering and sorting data from a sparse worksheet in Excel VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related