VBAExcellNoob
Board Regular
- Joined
- Feb 13, 2015
- Messages
- 117
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:
Sorry for the long post but I have been having trouble with this all day. Any help at all would be much appreciated!
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:
Code:
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 SubSorry for the long post but I have been having trouble with this all day. Any help at all would be much appreciated!
