Considering all rows even if hidden by autofilters

[aaw23]

Hi

The below is part of a function that all works fine, the workbook with the function is performing a lookup to another.
The problem I'm trying to solve is: the workbook where data is being pulled from, where there are multiple sheets that are named containing the text "BM" are always in some state of having autofilters applied. This function only considers the rows that are visible on the sheets BM. What I am seeking to add into the below is the code that would consider all rows on the sheets "BM", not just the visible ones.

So to have dBookSuth.Worksheets("BM" & i) be considered with all autofilters cleared.

And I just can't seem to crack it. Appreciate any assistance.



Set dBookSuth = Workbooks.Open(dFileNameSuth, , True)

Dim lastRow As Integer, dealList As Range, dcell As Range, frange As Range
lastRow = iSheet.Cells(100000, 12).End(XlDirection.xlUp).Row
If lastRow >= 7 Then
Set dealList = iSheet.Range("L7:L" & lastRow)
For Each dcell In dealList.Cells
If dcell.Value <> "" Then
Set frange = Nothing
For i = 1 To 6
Set dSheetSuth = dBookSuth.Worksheets("BM" & i)
If Not dSheetSuth Is Nothing Then
Set frange = FindValueInRange(dSheetSuth.Range("G:G"), dcell.Value)
If Not frange Is Nothing Then
GoTo processRow
End If
End If

 

[DanteAmor]

So to have dBookSuth.Worksheets("BM" & i) be considered with all autofilters cleared.

Add these lines:

  If Not dSheetSuth Is Nothing Then
    If dSheetSuth.AutoFilterMode Then dSheetSuth.AutoFilterMode = False
    If dSheetSuth.FilterMode Then dSheetSuth.ShowAllData
    dSheetSuth.Cells.EntireRow.Hidden = False
    Set frange = FindValueInRange(dSheetSuth.Range("G:G"), dcell.Value)

 

[aaw23]

That worked well, thanks very much for the quick reply and really helping out

 

[DanteAmor]

Im glad to help you. Thanks for the feedback