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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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.
 
Upvote 0
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.
 
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,223,903
Messages
6,175,286
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top