UserForms, Filtered Data and Visible Cells

[reberryjr]

I've been working on this issue for almost 4 hours this morning to no avail. Essentially, I have about 30 text boxes that I need to populate from 2 different worksheets based on data entered in a UserForm. I was able to get data to populate the UserForm as I wanted, until I introduced a filter for date range. I've been to multiple sites looking for a solution, but I haven't found one that solves for the entire equation. Here is a snippet of code where I tried to merge multiple solutions into 1, but it doesn't work. Suggestions?

Private Sub cmd_test_Click()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Dim m As Workbook
Dim mP As Worksheet
Dim mPFR As Range
Dim mPFRLR As Long

Set m = ThisWorkbook
Set mP = m.Sheets("PRF")

mP.Activate
'Sorts sheet by Mgr, Assoc, & Date
mP.UsedRange.Sort Key1:=Range("AG2"), Order1:=xlAscending, Key2:=Range("P2"), Order2:=xlAscending, Key3:=Range("S2"), Header:=xlYes
'Filters for date range entered in the form
mP.UsedRange.AutoFilter Field:=19, Criteria1:=">=" & Me.txt_Start, Operator:=xlAnd, Criteria2:="<=" & Me.txt_End
'Sets last row on filtered data
mPFRLR = mP.Range("A2:A" & Rows.Count).End(xlUp).Row
'Sets the visible rows as a range
Set mPFR = Cells.SpecialCells(xlCellTypeVisible).Range("A2:AG" & mPFRLR)

'With mPFR
    Me.txt_PAppInSLA = WorksheetFunction.CountIfs(mPFR.Range("P:P"), Me.cobo_Assoc, mPFR.Range("T:T"), "Approved", mPFR.Range("AF:AF"), "In SLA")
    Me.txt_PAppOutSLA = WorksheetFunction.CountIfs(mPFR.Range("P:P"), Me.cobo_Assoc, mPFR.Range("T:T"), "Approved", mPFR.Range("AF:AF"), "Out of SLA")
'End With



Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[sijpie]

Your post is a bit vague: what isn't working once you apply your filter? The boxes don't get populated? or the filter makes no difference to the contents of the boxes? something else??

 

[reberryjr]

Your post is a bit vague: what isn't working once you apply your filter? The boxes don't get populated? or the filter makes no difference to the contents of the boxes? something else??

My form's text boxes aren't populating with correct counts from their source worksheets. From the research I've done, it seems that the issue is CountIFS won't ignore hidden rows, so the counts are bloated. I'm struggling to find a solution to the issue, as I can't seem to find anything on the web that will explain how to count visible cells only, when I have 4 or 5 different pieces of criteria.

 

[sijpie]

With the following code you can copy the filtered table to a temporary sheet, which you then can use to populate the form textboxes.
If you understand arrays, then you can populate the form directly from the vOut output array (no need then to dump it to a sheet)

Sub CopyVisibleRowsFromFiltered()
    Dim vA, vOut
    Dim rR As Range, rA As Range
    Dim lR As Long, lOff As Long, lRa As Long, lC As Long, lRo As Long, UB1 As Long, UB2 As Long
    Dim wsTemp As Worksheet, wsThis As Worksheet
    
    Set wsThis = ActiveSheet
    'make an output array to the size of the full input table
    Set rR = Range("B1").CurrentRegion      '<<<< your top left corner of (filtered) range
    UB1 = rR.Rows.Count
    UB2 = rR.Columns.Count
    ReDim vOut(1 To UB1, 1 To UB2)
    
    'set rR to the filtered table
    Set rR = Range("b1").CurrentRegion.SpecialCells(xlCellTypeVisible)
    
    lRo = 1
    'Go through each area of the filtered table (an area is a contiguous block in the filtered range)
    For Each rA In rR.Areas
        lR = rA.Rows.Count
        'read area into an array
        vA = rA.Value
        'copy this array to the output array
        For lRa = 1 To lR
            For lC = 1 To UB2
                vOut(lRo, lC) = vA(lRa, lC)
            Next lC
            lRo = lRo + 1
        Next lRa
    Next rA
    
    'check for the temporary output sheet
    For Each wsTemp In Sheets
        If wsTemp.Name = "TempFiltered" Then
            Exit For
        End If
    Next wsTemp
    If wsTemp Is Nothing Then   'does't exist, so create it
        Sheets.Add
        Set wsTemp = ActiveSheet
        wsTemp.Name = "TempFiltered"
    End If
    'clear curent contents and paste new
    wsTemp.Range("b1").CurrentRegion.Clear
    wsTemp.Range("b1").Offset(15, 0).Resize(UB1, UB2).Value = vOut
    wsThis.Select
End Sub