Thanks in advance for your patience with me as I'm new to these forums, and pretty new to Macros/VBA in Excel.
I have a workbook with multiple sheets that all feed data into two main sheets, called 'Timeline' and 'Dashboard'
I have a macro to run an AutoResize function on 'Dashboard' rows:
Sub AutoResize()
Dim rRow As Range
With Range("DashCells").EntireRow
.AutoFit
For Each rRow In .Rows
If rRow.RowHeight < 19.5 Then rRow.RowHeight = 19.5
Next rRow
End With
End Sub
Display More
And then I have the page code of each data source sheet execute the AutoResize macro when any of three different cells are modified on the sheets. From the page code:
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$F$6", "$F$13", "$F$14": AutoResize
Case Else: Exit Sub
End Select
End Sub
So my question is this: I have the following NEW macro called AutoFilter, created to run an auto filter to hide any rows where values of "#N/A" are present from the 'Timeline' sheet:
When I run it manually, it filters out rows with "#N/A" just fine. So I want to then make an addition to the page code above to execute AutoFilter if a range of cells are changed. I tried the following (copied the WHOLE sheet code) but it doesn't work when I modify the cell in the column identified:
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$F$6", "$F$13", "$F$14": AutoResize
Case Else: Exit Sub
End Select
End Sub
Private Sub Worksheet_Change1(ByVal Target As Range)
Select Case Target.Address
Case "$C$18:$C$56": AutoFilter
Case Else: Exit Sub
End Select
End Sub
Display More
With this, the first portion still works (I modified one of the cells, and it executed the AutoResize macro just fine) - but if I enter data into the column that should trigger the AutoFilter macro, it doesn't fire off. I go to the 'Timeline' sheet, and see the new row isn't visible. But when I open up the Macros and run AutoFilter manually, it pops up just fine.
I have tried just using Case "$C$18" in case ranges were not possible with this code, but that didn't help. I also tried doing just the Worksheet_Change1 code in a blank sheet using A1 as the reference trigger for firing off the AutoFilter macro, but that did not work either.
So that's my issue - what am I missing to make my life a whole lot easier on this end? Thanks in advance for any help, and please do let me know if I'm missing any information needed to assist me.
(Per the rules, I've also asked this question at ExcelForum.)