Selection Change Macro Error when Autofilter Hides Row

ErinKSimmons

New Member
Joined
Oct 9, 2019
Messages
9
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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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?
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

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