Autofilter data and put the numbers of visible rows into an array

[Duchesssix]

I have not used arrays yet in VBA. However, I think using an array will be fast then the loop I am using now to determine the row number of the next visible row of autofiltered data.

For example: I filter a set of data on specified fields and the results show the following rows as visible:
2
4
6
7
10

How to I get 2,4,6,7,10 into an array that I can loop through instead of looping for visible rows only.

The followng is what I currently have for code without using an array:

ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.EntireRow.Hidden = False
ActiveCell.Offset(1, 0).Select
Loop
nrow = ActiveCell.Row
intAddress = nrow

 

[Domenic]

Try...

RowNum = Range(ActiveCell.Offset(1, 0), Cells(Rows.Count, ActiveCell.Column)).SpecialCells(xlCellTypeVisible).Row

By the way, RowNum should be declared as data type "Long" (ie. Dim RowNum as Long), since the number of rows can exceed 32,767.

 

[Duchesssix]

Thanks but I get the error "No cells Found" when it tris to execute that line of code

 

[Domenic]

So that means that there are no visible rows after the active cell. In that case, try something like this...

[font=Courier New][color=darkblue]Dim[/color] RowNum [color=darkblue]As[/color] [color=darkblue]Long[/color]
  
[color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]Resume[/color] [color=darkblue]Next[/color]
RowNum = Range(ActiveCell.Offset(1, 0), Cells(Rows.Count, ActiveCell.Column)).SpecialCells(xlCellTypeVisible).Row
[color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]GoTo[/color] 0

[color=darkblue]If[/color] RowNum = 0 [color=darkblue]Then[/color]
    MsgBox "There are no visible rows after the active cell...", vbExclamation
[color=darkblue]Else[/color]
    MsgBox "The next visible row is Row " & RowNum, vbInformation
[color=darkblue]End[/color] [color=darkblue]If[/color]
    [/font]

 

[Duchesssix]

Can you suggest a way to do this using an array? I want to speed the process up and I think getting the visible data into an array with the raw number for each visible row would significantly help.

 

[Domenic]

Unclear... Do you want the first visible row, or do you want to fill an array with the row numbers of the visible rows? Actually, what are you ultimately trying to achieve?

 

[Duchesssix]

Domenic, the latter. I want to fill an array with the row numbers of all the visible rows. Basically I have different userforms that filter on different criteria based on the data source that will be used to enter the data. I want the array to have the row numbers of all visible rows meeting the specific filter criteria so that any fields already populated with data can be brought into the userform so the user knows that the data has already been entered and doesn't spend time "re-entering" it.

Does this make sense?

Currently I use a loop to loop through the visible rows and put the row number into a variable. This works but is a bit slow when there are many hidden rows in between two visible rows. I thought putting row numbers into an array would speed up the previous and next record buttons.

Thanks in advance for any assistance or guidance you can provide.

 

[Domenic]

I'm still not clear as to what you're trying to ultimately achieve. In any case, unless I'm mistaken, it looks like you're actually looping through each row, whether visible or not, instead of looping through only the visible rows. If so, then we can limit the looping to visible rows by using the SpecialCells method...

[font=Courier New][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] test()

    [color=darkblue]Dim[/color] MyArray() [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] Rng [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] Cell [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] LastRow [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] Cnt [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    [color=green]'Your code to filter the data here[/color]
    
    [color=darkblue]With[/color] Range("A1:A" & LastRow)
        [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]Resume[/color] [color=darkblue]Next[/color]
        [color=darkblue]Set[/color] Rng = .Resize(.Rows.Count - 1).Offset(1, 0).SpecialCells(xlCellTypeVisible)
        [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]GoTo[/color] 0
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
    [color=darkblue]If[/color] [color=darkblue]Not[/color] Rng [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
        [color=darkblue]For[/color] [color=darkblue]Each[/color] Cell [color=darkblue]In[/color] Rng
            Cnt = Cnt + 1
            [color=darkblue]ReDim[/color] [color=darkblue]Preserve[/color] MyArray(1 [color=darkblue]To[/color] Cnt)
            MyArray(Cnt) = Cell.Row
        [color=darkblue]Next[/color] Cell
    [color=darkblue]Else[/color]
        MsgBox "No filtered data is available!", vbExclamation
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
    [color=green]'Do something with the variable MyArray[/color]

[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

 

[Duchesssix]

Domenic,

With your help I was finally able to create an array with the information that I need. Basically, the array contains just the row number of the records I need to work with.

My new problem is how to access the data in the array in all my subroutines/functions?

From what I understand you can't declare a Public Array in the general declarations of the userform. What is the most efficient way to access the array data without having to pass the Array to each subroutine. This is important as events will need to access the Array as well.

Thanks again for your assistance.

 

[Domenic]

Declare the array as Public in a standard module. Make sure that it's declared at the beginning of the module, before any procedure.