Drag Formula Down and ignore hidden/invisible rows

Status
Not open for further replies.

jkharel

New Member
Joined
Aug 30, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Hello all,
I am trying to write a macro that filters a sheet and uses formulas like SUMIF and Vlookup to replace the existing columns. I need to find a way to drag down the formulas without affecting the hidden/ invisible cells in vba. I would really appreciate any help and suggestion. Here is my code so far. I am dragging the formulas but couldn't find a way to skip hidden/invisible rows. Thank you so much for your time.
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 ignore the hidden cells
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,045
Office Version
  1. 2019
Platform
  1. Windows
but couldn't find a way to skip hidden/invisible rows.
Does that mean that you don't want the formula entered into hidden rows of you don't want the formula to look at what is in the hidden rows?

I'm assuming the latter because the first one shouldn't need any special coding. If that is correct then we might need a data sample to go with the formulas so that they can be rewritten and tested.
 

jkharel

New Member
Joined
Aug 30, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Does that mean that you don't want the formula entered into hidden rows of you don't want the formula to look at what is in the hidden rows?

I'm assuming the latter because the first one shouldn't need any special coding. If that is correct then we might need a data sample to go with the formulas so that they can be rewritten and tested.
Hello @jasonb75. I want the formulas not to be entered into hidden rows and only to visible rows in filtered data. I am sorry if I didn't understand but doesn't formula not to entered into hidden rows mean it will skip the hidden rows and not look at it? Also I could not understand what you meant by ' Formulas provided for Excel 2019 unless your profile shows an older version. ' Thank you for your time and help.
 
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,114,470
Messages
5,548,207
Members
410,824
Latest member
Bobmn4
Top