Solved: Occasional filter\sort on worksheet_change glitch

[Phelony]

Hi Guys

I've got a spreadsheet that displays data and is filterable through several drop down menu options. (Options are on the form but the change event criteria are on the sheet)

On a second sheet, is an indirect formula that dictates the data displayed on this worksheet, the worksheet notes the change, alters the number of visible rows accordingly and then filters, sorts and sets the control columns to values (this is done to preserve an array formula that acts as a trigger for the rest of the sheet).

However, on occasion (and I honestly cannot say what occasions as there seems to be no pattern but I refuse to believe it's just random chance) the filter\sort doesn't function on the first attempt, but is ALWAYS fine on the second.

Ideas?

Apologies if this isn't the clearest description, but I hope you get a general idea. (Cannot post the file due to the nature of the data contained within, and it wouldn't function without the data!)

I know this is slightly overcomplicated, however, you should see what it's replacing! Any guidance would be appreciated.

Phel

[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Sheets("form").Activate
If Not Intersect(Range("A1"), Target) Is Nothing Then
'set variables
'Target = Range("B1")
Dim hiderange As Integer
Dim c As Long
Dim periodq As String
'set sheet variant name(s)
periodq = Sheets("form").Range("A2")
'clear conditions
Range("A3").ClearContents
Range("A4").ClearContents
Rows("12:111").Hidden = True

'ensure that column B is reporting correctly by rewriting the formula and copying
'to all relevant cells
'lock number indicators
Range("AI12") = "=AI11+1"
Range("AI12").Copy
Range("AI12:AI111").PasteSpecial xlPasteFormulas

Range("AI12:AI111").Copy
Range("AI12:AI111").PasteSpecial xlPasteValues
'lock area indicators
Range("B12") = "=INDEX('Target Data'!C:C,MATCH(Form!A12,'Target Data'!D,0),1)"
Range("B12").Copy
Range("B12:B111").PasteSpecial xlPasteFormulas

Range("B12:B111").Copy
Range("B12:B111").PasteSpecial xlPasteValues

'consults a table on the "data" sheet and counts the number of times the value in
c = Sheets(periodq).Range("B" & Rows.Count).End(xlUp).Row
If c < 1 Then c = 1
Range("A4") = Application.WorksheetFunction.CountIf(Sheets(periodq).Range("$B$2:B" & c), Sheets("Form").Range("A1"))
'hide rows calculation
hiderange = Range("A4")
Rows("12:" & 11 + hiderange).Hidden = False

Application.ScreenUpdating = True

'set autofilter conditions and apply
Selection.AutoFilter Field:=1, Criteria1:="<>#N/A", Operator:=xlAnd

'set conditions for sorting data into alphabetical order
Range("A12:Ai112").Select

Selection.Sort Key1:=Range("B12"), Order1:=xlAscending, Key2:=Range("A12" _
), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal

Application.ScreenUpdating = True
Range("A1").Activate
End If
End Sub
[/VBA]

[Bob Phillips]

One thing I notice is that you are using Selection to drive the autofilter, but I cannot see where you select anything. Presumably this means A1 is still active, which is Target, so why not use Target.

Other than that and simplifying the code a bit, it is hard to offer anything helpful without seeing the workbook and knowing when it happens.

[Phelony]

Thanks Xld

Range B12:B111 is still selected from the pastevalues section earlier, that contains names, column A of range A12:AI112 which is then selected contains the subgroups they then need to be sorted into.

I've had to purposefully keep the code quite "chunky" as it's a project to be handed over and supported by someone else so I went for a more basic "does what it says on the tin" approach.

However, having read your comments and re-read the code, the autofilter section isn't particularly well structured and is most likely the cause, I can see how under certain conditions the data wouldn't get sorted correctly on the first run, hence the second one always sorting it out.

No so much of a mystery then, just me being stupid!

Thanks for the input.

Phel