Applying Formulas after Filtering Data

[jkharel]

I am trying to apply formulas to my data after filtering my data. I apply a formula to one of the columns in the data and use the range like "A2:AlRow" where lRow is the last row of the data sheet. I was assuming the 2 and lastRow will be used relatively and lRow wil be updated when I filter the data. However, the formula is applied to the whole data and unfiltered rows are affected as well. Can anyone please suggest me a way of applying formula to just filtered data only. The whole sheet has thousands of rows but after I filter I want the formula to be applied only to filtered rows. How could I use formula to filtered data set not knowing , after filtering, what the absolute row number of first and last row is on the filtered data set

 

[Toadstool]

Hi JKHarel,

You don't give an example of your formulae but have you looked at using SUBTOTAL and/or AGGREGATE functions?

 

[jkharel]

Hi JKHarel,

You don't give an example of your formulae but have you looked at using SUBTOTAL and/or AGGREGATE functions?

Hello @Toadstool. I researched on subtotal/aggregate and couldn't find a way to integrate that with my code. I found the first row and last row of filtered data and was thinking of doing the autofill. Now I need to find a way to do autofill but ignoring the hidden rows because sometimes the row numbers in my filtered data are not consecutive.
Here is my code so far.
Sub ReconcileColumns()
'
' ReconcileColumns Macro
'

'
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim fPath As Variant
Dim Val As Variant
Dim vendor As String
Dim fName As String
Dim fso As Object
Dim DT As String
Dim fRow As Long
Dim lastRow As Long
Dim AgentBreakdownlRow As Long
Dim CompareAgentBreakdownlRow As Long

Set fso = CreateObject("Scripting.FileSystemObject")
'Open file you would like to reconcile
fPath = Application.GetOpenFilename(filefilter:="Excel Files,*.xl*; *.xm*")
' MultiSelect:=True)
If fPath <> False Then
fName = fso.GetFileName(fPath)
Workbooks.Open fileName:=fPath
End If
'Unfilter sheet only if there is a filter
If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
'last row of newly opened file
AgentBreakdownlRow = Workbooks(fName).Worksheets("Per Agent Per Day Hr Breakdown").Range("C" & Rows.Count).End(xlUp).Row

'Give everyone the same date and week
Workbooks(fName).Worksheets("Per Agent Per Day Hr Breakdown").Range("H2:I2").Copy Workbooks(fName).Worksheets("Per Agent Per Day Hr Breakdown").Range("H3:I" & AgentBreakdownlRow)

'Sort A - Z Vendor file
Workbooks(fName).Worksheets("Per Agent Per Day Hr Breakdown").Range("A1:Q" & AgentBreakdownlRow).Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlYes

'Format date cells
Workbooks(fName).Worksheets("Per Agent Per Day Hr Breakdown").Range("I:I").NumberFormat = "m/dd/yyyy"

''last row of master sheet
lRow = Workbooks("Vendor Hours Reconciliation Week MASTER - Cumulative.xlsm").Worksheets("Per Agent Per Day Hr Breakdown").Range("A" & Rows.Count).End(xlUp).Row
'
''Check what vendor and filter it accordingly in per agent breakdown sheet
vendor = Workbooks(fName).Worksheets("Per Agent Per Day Hr Breakdown").Range("A2").Value
DT = Workbooks(fName).Worksheets("Per Agent Per Day Hr Breakdown").Range("I2").Value
'Apply Vendor Filter
Workbooks("Vendor Hours Reconciliation Week MASTER - Cumulative.xlsm").Worksheets("Per Agent Per Day Hr Breakdown").Range("A1:S" & lRow).AutoFilter Field:=1, Criteria1:=vendor

'Apply Date Filter
Workbooks("Vendor Hours Reconciliation Week MASTER - Cumulative.xlsm").Worksheets("Per Agent Per Day Hr Breakdown").Range("A1:S" & lRow).AutoFilter Field:=9, Criteria1:=DT

'Clear the sorted field
Workbooks("Vendor Hours Reconciliation Week MASTER - Cumulative.xlsm").Worksheets("Per Agent Per Day Hr Breakdown").AutoFilter.Sort.SortFields.Clear

'Sort A - Z Master file
Workbooks("Vendor Hours Reconciliation Week MASTER - Cumulative.xlsm").Worksheets("Per Agent Per Day Hr Breakdown").AutoFilter.Sort.SortFields.Add Order:=xlAscending, _
SortOn:=xlSortOnValues, Key:=Workbooks("Vendor Hours Reconciliation Week MASTER - Cumulative.xlsm").Worksheets("Per Agent Per Day Hr Breakdown").Range("E1:E" & lRow)

'Applying the sort
Workbooks("Vendor Hours Reconciliation Week MASTER - Cumulative.xlsm").Worksheets("Per Agent Per Day Hr Breakdown").AutoFilter.Sort.Apply



'adjust training hours adjustment column of master sheet based on columns total training hours and training hours
'adjustment of vendor sheet


Workbooks("Vendor Hours Reconciliation Week MASTER - Cumulative.xlsm").Activate
Worksheets("Per Agent Per Day Hr Breakdown").Select
fRow = ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 1).Row
lastRow = Worksheets("Per Agent Per Day Hr Breakdown").Range("A" & Rows.Count).End(xlUp).Row
MsgBox fRow
MsgBox lastRow


The formulas I am trying to use are
'Range("O2").Formula = "=SUMIF('[" & fName & "]Per Agent Per Day Hr Breakdown'!$C:$C, E2, '[" & fName & "]Per Agent Per Day Hr Breakdown'!$J:$J) + SUMIF('[" & fName & "]Per Agent Per Day Hr Breakdown'!$C:$C, E2, '[" & fName & "]Per Agent Per Day Hr Breakdown'!$O:$O) - J2"
'Range("O2").Select
'Selection.AutoFill Destination:=Range("O2" & ":O" & lRow), Type:=xlFillDefault

'With Range("O2" & ":O" & lRow)
' .Value = .Value
'End With

' Range("N" & fRow).Formula = "=SUMIF('[" & fName & "]Per Agent Per Day Hr Breakdown'!$C:$C, E" & fRow & ", '[" & fName & "]Per Agent Per Day Hr Breakdown'!$K:$K) + SUMIF('[" & fName & "]Per Agent Per Day Hr Breakdown'!$C:$C, E" & fRow & ", '[" & fName & "]Per Agent Per Day Hr Breakdown'!$N:$N) - K" & fRow & ""
'Range("N" & fRow).Select
'Selection.AutoFill Destination:=Range("N" & fRow & ":N" & lastRow), Type:=xlFillDefault

'With Range("N" & fRow & ":N" & lastRow)
' .Value = .Value
'End With


'Range("M" & fRow).Formula = "=VLOOKUP(E&fRow, '[" & fName & "]Per Agent Per Day Hr Breakdown'!$C:$M, 11, FALSE)"
''Range("M" & fRow).Select
'Selection.AutoFill Destination:=Range("M" & fRow & ":M" & lastRow), Type:=xlFillDefault

'With Range("M" & fRow & ":M" & lastRow)
'.Value = .Value
'End With

//drag down the formula but ignore the hidden cells