0

I have the below code and it works perfectly for my purposes except when an AutoFilter is applied to the sheet & row 2 is hidden by the AutoFilter.

The Macro inserts the 'Record_Created_Year' and '=Year(##)' into the 1st empty Column & Row 1/2 correctly (even if the Row is hidden), however does not FillDown the calculations to the last row.

Instead of filling down the calculations added into Row 2, it is instead selecting the 2nd visible row (which could be Row 3) and filling down the values within those cells (which are blank, but for testing purposes I populated them to prove the theory).

I have been able to work around the issue by including a command "ActiveSheet.AutoFilterMode = False", however that is causing some issues for a subset of users who need to reapply complex filters after running the macro.

Is there a better way to define the FillDown Range, so it ignores the Auto Filter and actually Fills Down the data/calculations within Row2 rather than the data contained within the 2nd visible row?

Cheers in advance for any assistance.

Private Sub Add_and_Filldown_Calcs()

Dim LastCol As Long
    With ActiveSheet
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    End With
Dim LastRow As Long
    With ActiveSheet
        LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    End With
Dim rng As Range
Set rng = ActiveSheet.Cells
Dim Created_Date As Range
Set Created_Date = ActiveSheet.Range("A1:BZ1").Find("sys_created_on", lookat:=xlWhole)

rng.Parent.Cells(1, LastCol + 1).Value = "Record_Created_Year"
rng.Parent.Cells(2, LastCol + 1).Formula = "=year(" & Replace(Created_Date.Address(False, False), "1", "") & "2)"
rng.Parent.Cells(1, LastCol + 2).Value = "Record_Created_Month"
rng.Parent.Cells(2, LastCol + 2).Formula = "=Month(" & Replace(Created_Date.Address(False, False), "1", "") & "2)"
' Many others removed to make it easier to read
'Filldown new calcs

Dim LastColAfterCalulations As Long
    With ActiveSheet
        lastcolaftercalculations = .Cells(1, .Columns.Count).End(xlToLeft).Column
    End With

Range(Cells(2, LastCol + 1), Cells(LastRow, lastcolaftercalculations)).FillDown

End Sub
Ralph
  • 9,284
  • 4
  • 32
  • 42
J Clark
  • 3
  • 3
  • 1
    You could populate all rows initially: `rng.Parent.Cells(2, LastCol + 1).Resize(lastrow - 1).Formula = ...` and so on. – Rory Apr 27 '16 at 15:20
  • Rory, thank you for your suggestion. I have tried that and it changed the output however has not totally resolved the issue. Instead of the original calculation being added into Row2 when the row was hidden, it leaves Row2 empty, but applies the calculation to all Visible Rows. So any hidden rows are left blank, with visual rows updated to contain the calculation. As for advise on the function 'Resize' however, thats great, I am sure I can use that in the future to make things easier for me. – J Clark Apr 27 '16 at 15:33
  • Apologies - I had forgotten about that abomination (someone at Redmond needs a kick for that). With the filter applied, you'll have to loop through the cells, I'm afraid. – Rory Apr 27 '16 at 16:01

1 Answers1

0

A workaround could be to first copy the invisible row 2 to the visible row 3 end then fill Down. Something like:

Range(Cells(2, LastCol + 1),Cells(2,lastcolaftercalculations)).copy Range(Cells(3, LastCol + 1,Cells(2,lastcolaftercalculations))
Range(Cells(3, LastCol + 1), Cells(LastRow, lastcolaftercalculations)).FillDown

This does however not fill other filtered cells. When you want that you could use this code, that will first disable the autofilter and then reset it to the previous setting after you run your code.

Community
  • 1
  • 1
Marco Vos
  • 2,888
  • 1
  • 9
  • 10
  • Marco, thank you - the code to determine, store and reapply the original Auto Filter Settings works a treat and has resolved my issue. Your assistance is really appreciated. – J Clark Apr 28 '16 at 09:22