Problem with finding last row with filtered rows

[VBAExcellNoob]

Hi guys,

I'm currently having a problem with finding the last row of a column that has filtered data in it.
If the last row in the filtered data isn't the last row of the unfiltered data set my code does not work properly. I've included some screen shots of the data as I can't explain it very well.

In the first image the data has been filtered for the 30/01/2015 and it calculates the correct amount of pass and fail and stores it after the last row in the column.

In the second image I have filtered the data for the 23/01/2015, however as you can see it does not display the pass/fail rate. It is reading in row 236 as the last row instead of row 248.

Here is the 3rd image showing what is going on when the wrong last row is selected

Here is the code I have written as well:

Private Sub CommandButton4_Click()
'Filters to a user inputted date
Dim i As Integer
Dim j As Integer


myDate = InputBox("Please enter date in the FORMAT MM/DD/20YY")


i = Worksheets.count


For j = 2 To i
With Sheets(j)


.Range("$A$1:$AU$247").AutoFilter Field:=42, Criteria1:=">=" & Format(myDate, "mm/dd/yyyy"), _
Operator:=xlAnd, Criteria2:="<=" & Format(myDate, "mm/dd/yyyy")


End With
Next j
End Sub


Private Sub CommandButton2_Click()
'Counts the number of Pass and Fail
Dim lastRow As Long
Dim ws As Worksheet
Dim rng As Range
Dim rng2 As Range
Dim countPass As Integer
Dim countFail As Integer
Dim total As Integer
Dim totalPass As Double
Dim totalFail As Double


Set ws = Worksheets("Absence")


lastRow = ws.UsedRange.SpecialCells(xlCellTypeLastCell).Row
Set rng = ws.Range("AT2:AT" & lastRow)
Set rng2 = ws.Range("AP2:AP" & lastRow)


ws.Range("AT" & Rows.count).End(xlUp)(3).Font.Bold = True
ws.Range("AU" & Rows.count).End(xlUp)(3).Font.Bold = True


ws.Range("AT" & Rows.count).End(xlUp)(3) = "Pass"
ws.Range("AU" & Rows.count).End(xlUp)(3) = "Fail"


countPass = Application.WorksheetFunction.CountIfs(rng, "Pass", rng2, myDate)
countFail = Application.WorksheetFunction.CountIfs(rng, "Fail", rng2, myDate)


total = countPass + countFail
totalPass = countPass / total
totalFail = countFail / total


ws.Range("AT" & Rows.count).End(xlUp)(2) = countPass
ws.Range("AU" & Rows.count).End(xlUp)(2) = countFail
ws.Range("AT" & Rows.count).End(xlUp)(2) = FormatPercent(totalPass)
ws.Range("AU" & Rows.count).End(xlUp)(2) = FormatPercent(totalFail)




End Sub

Sorry for the long post but I have been having trouble with this all day. Any help at all would be much appreciated!

 

[AlphaFrog]

I am wondering if you saw the code I posted in Message #6 and whether it does what you want or not?

I think he wants visible rows below the filtered range. Not the top\bottom visible rows in the filtered range.

 

[VBAExcellNoob]

I think he wants visible rows below the filtered range. Not the top\bottom visible rows in the filtered range.

AlphaFrog is correct, thats what I was looking for. I'm not sure if your solution is what I need, I'll have a look at it though!