Scrolling only through visible rows

[UniMord]

I have a small summary table ("Ind_Claim") that formats individual records from a much larger table ("All_Claims").

Ind_Claim's formulas feed off a named cell ("Cur_Row"), which contains the number of the table row currently under consideration. Cur_Row can be changed manually, or by means of a scrollbar.

All_Claims is usually filtered, and I'd like the scrollbar to proceed only through its visible rows, which might number only a few dozen, rather than through thousands of rows.

(If it simplifies matters at all, the primary key of All_Claims ("Num") contains the record's number, so, for example, the 3,456th row of the table, will contain 3,456 in the Num column.)

Any help will be gratefully appreciated.

 

[Jerry Sullivan]

Hello UniMord,

You could try placing your visible Record Numbers in an array; then use the scroll bar value
as the array index to look up the corresponding Record Number.

Private Sub ScrollBar1_Change()
    Dim i As Long
    Dim rngVisible As Range, c As Range
    Dim varArray As Variant
 
    Set rngVisible = Range("All_Claims[Num]").SpecialCells(xlCellTypeVisible)
    ReDim varArray(1 To rngVisible.Count)
    For Each c In rngVisible
        i = i + 1
        varArray(i) = c.Value
    Next
 
    ScrollBar1.Min = 1
    ScrollBar1.Max = UBound(varArray)
    Range("Cur_Row").Value = varArray(ScrollBar1.Value)
    Set rngVisible = Nothing
    Erase varArray
End Sub

 

[UniMord]

Thanks Jerry!

I was working on this last night, and came up with code that was similar in prinicipal, but not as efficient as yours - this is why I was looking for help.

Ideally, I was hoping that it would be possible to use a Static or Global array, and detect if the filter has changed or not, so that it shouldn't have to be reloaded on every call. Do you know if this is possible and practical? Right now I'm testing on a small sample dataset, so it's pretty quick, but when you get to 30,000 rows or so, the inefficiencies start to rear their ugly heads.

Also, let me ask you a few questions...

1. What is the advantage of declaring: varArray As Variant? I'm under the impression that that's only useful for quickstacking with the Array() function. I had declared: varArray() As Long, since that's really how we intend to use it.
2. Is it necessary to "Set rngVisible = Nothing" and "Erase varArray"? Don't those get cleaned up automatically upon termination?
3. The code doesn't behave so well when the filter's been changed between calls. Do you know how to make it accomodate the change? I'm thinking of checking if the Spinner's value is less than or more than Cur_Row, and loading the array, from lowest to highest, or vice versa, and using Application.WorksheetFunction.Match to find the closest match. Does that sound reasonable? Do you have a more efficient way to go about it?

 

[Jerry Sullivan]

Hi UniMord,

Ideally, I was hoping that it would be possible to use a Static or Global array, and detect if the filter has changed or not, so that it shouldn't have to be reloaded on every call. Do you know if this is possible and practical? Right now I'm testing on a small sample dataset, so it's pretty quick, but when you get to 30,000 rows or so, the inefficiencies start to rear their ugly heads.

That concern crossed my mind as well. I tested with 100,000 rows and was surprised that I didn't observe any delay. That being said, you're right that ideally it wouldn't have to reload each time. I'd suggest you try it as is first with your larger data set and see if there is a problem that needs fixing. If you get an instant response, a Global array might not be worth the added complexity.

What is the advantage of declaring: varArray As Variant? I'm under the impression that that's only useful for quickstacking with the Array() function. I had declared: varArray() As Long, since that's really how we intend to use it.

Good point. The origin of that is my initial attempt to read more directly from the range into the varArray. I tried:

Set rngVisible = Range("All_Claims[Num]").SpecialCells(xlCellTypeVisible)
varArray = Application.Transpose(rngVisible)

I learned that doesn't work for a non-contiguous range of cells. When I changed the code to a For...Next loop, I just didn't redefine the data type. One slight benefit of the Variant declaration is that it will work for non-numeric lists. While I was doing some testing of this, I changed the reference column values to A,B,C... and it also worked. This wouldn't benefit your current application, but could be useful if the code is re-purposed.

Is it necessary to "Set rngVisible = Nothing" and "Erase varArray"? Don't those get cleaned up automatically upon termination?

I would love it if someone would point us to a definitive answer to this question. I have seen differing opinions on this topic by people with more experience than me. One school of thought (that could be a myth) is that memory reserved for the object is supposed to be released when the procedure ends..but sometimes isn't.
This probably deserves its own thread. When I see polished code from experts, they tend to set the objects to Nothing in a clean up step, so I figure it is better to be safe and follow suit.

The code doesn't behave so well when the filter's been changed between calls. Do you know how to make it accommodate the change? I'm thinking of checking if the Spinner's value is less than or more than Cur_Row, and loading the array, from lowest to highest, or vice versa, and using Application.WorksheetFunction.Match to find the closest match. Does that sound reasonable? Do you have a more efficient way to go about it?

Hmmm...I hadn't even considered what happens when the filter changes. It seems that this would depend on what action you would want to happen to your Ind_Claim Table.If you have All_Claims filtered by one client and Cur_Row = 20,000, and then you switch to filter by a different client. There wouldn't be much benefit in finding the closest match of 19,972. Depending on how you use this, it might be better to go to the first visible record.

Interesting puzzle!