Applying Formulas after Filtering Data

jkharel

New Member
Joined
Aug 30, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
993
Office Version
  1. 2016
Platform
  1. Windows
Hi JKHarel,

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

jkharel

New Member
Joined
Aug 30, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,113,838
Messages
5,544,603
Members
410,626
Latest member
rkmadasu
Top