Selection Change Macro Error when Autofilter Hides Row

ErinKSimmons

New Member
Joined
Oct 9, 2019
Messages
1
I have a selection change macro in a file that updates only when the selection occurs in the "NIIN" column of values. The macro works fine unless you apply a filter (from the preapplied AutoFilter) that hides the row that is currently being graphed and even if I select a now visible cell it continues to error.

I have tried to handle it by selecting F11 in the gray and then moving down to the first visible row, but that isn't the answer.

Any help is appreciated. Also if there is a better way to achieve updating a graph of one row based on the currently selected row, I am open to ideas.


Code:
Private Sub Worksheet_SelectionChange(ByVal NIINSelected As Range)
On Error GoTo ErrorHandler


' The variable NIINCells contains the cells that will
    ' cause graph to update
    
    'Find the last non-blank cell in column F(6)
    Dim LastRow As Long
    LastRow = Cells(Rows.Count, 6).End(xlUp).Row
        
    Set NIINCells = Range("F12:F" & LastRow)


If NIINSelected.Count > 1 Then
Exit Sub
End If


If Not Application.Intersect(NIINCells, Range(NIINSelected.Address)) _
           Is Nothing Then
    Dim NIINRow As Long
    NIINRow = NIINSelected.Row
    ActiveSheet.ChartObjects("NIINDmdChart").Activate
    ActiveChart.FullSeriesCollection(1).Values = NIINSelected.Offset(0, 18).Resize(1, 3)
    ActiveChart.HasTitle = True
    ActiveChart.ChartTitle.Text = NIINSelected.Value & " - " & NIINSelected.Offset(0, 4).Value & " Demand"
    NIINSelected.Select
End If
Exit Sub


ErrorHandler:
Range("F11").Select
ActiveCell.Offset(1, 0).Select
    Do Until ActiveCell.EntireRow.Hidden = False
    ActiveCell.Offset(1, 0).Select
    Loop


End Sub
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,573
What you could do is set the graph range to a range in a hidden sheet. Call the range, say, NIINGraphInp.
Then in your selectionChange sub when a cell is selected copy the values of NIINSelected.Offset(0, 18).Resize(1, 3) to this NIINGraphInp
Code:
    NIINGraphInp.value = NIINSelected.Offset(0, 18).Resize(1, 3).value
When a filter is applied to the column, that then won't affect the input range for the graph. Would that work?
 

Forum statistics

Threads
1,089,560
Messages
5,408,981
Members
403,247
Latest member
prabutr28

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top