trjhtr

I have a spreadsheet containing ±100 named ranges (5 rows, 15 colums) which are sorted based on time.

My plan was to make toggle buttons to hide or unhide named ranges when they contain a certain value.

So when I select the toggle button for 'Opel' for instance, all the named ranges containing 'Opel' are displayed. And when I select 'Opel' and 'Ford. All the ranges containing 'Opel' and 'Ford' are displayed while also being able to sort the ranges in that view.

For the sorting I use the following code:

Sub SorterenOpdrachten()

Dim Detail As Worksheet
Dim I As Long
Dim ListRng As Range
Dim LijstWks As Worksheet
Dim NamedRng As Name
Dim R As Long
Dim Rng As Range
Dim SortWks As Worksheet


'Worksheet declareren als variabele
Set Detail = Worksheets("detail")
Set LijstWks = Worksheets("LijstWks")
Set SortWks = Worksheets("SortWks")

'Startrij voor de lijst instellen = Rij 1 fungeert als "hoofding"
R = 2



'Ranges naar lijst kopiëren - Opdracht en uur
For Each NamedRng In ActiveWorkbook.Names



LijstWks.Cells(R, 1) = NamedRng.Name
LijstWks.Cells(R, 2) = NamedRng.RefersToRange.Cells(1, 2)
R = R + 1
Next NamedRng

'Ranges sorteren in de lijst
R = R - 1
Set ListRng = LijstWks.Range("A2").Resize(R - 1, 2)
ListRng.Sort Key1:=ListRng.Cells(1, 2), Order1:=xlAscending


'Ranges kopiëren naar SortWks
R = 1
For I = 1 To ListRng.Rows.Count
Set Rng = ActiveWorkbook.Names(ListRng.Cells(I, 1).Text).RefersToRange
Rng.Copy
SortWks.Cells(R, 1).PasteSpecial Paste:=xlPasteAll
R = R + Rng.Rows.Count
Next I

'Opdrachten naar detail kopiëren
R = 1
Worksheets("SortWks").Range("A1:T499").Copy 
Worksheets("detail").Range("A5:T504")

Next intCounter
End Sub

This works fine.

But when I use it in combination with the toggle buttons it is too slow.

For the toggle buttons I use the following code:

Sub Tegels()

Dim nm As Name

For Each nm In Application.Names
Range(nm).EntireRow.Hidden = True
Next nm


If TglOpel Then
For Each nm In Application.Names
If Application.CountIf(Range(nm), "*" & "Opel" & "*") Then 
Range(nm).EntireRow.Hidden = False
Next nm
End If

If TglChevrolet Then
For Each nm In Application.Names
If Application.CountIf(Range(nm), "*" & "Chevrolet" & "*") Then 
Range(nm).EntireRow.Hidden = False
Next nm
End If

If TglFord Then
For Each nm In Application.Names
If Application.CountIf(Range(nm), "*" & "Ford" & "*") Then 
Range(nm).EntireRow.Hidden = False
Next nm
End If

If TglBuick Then
For Each nm In Application.Names
If Application.CountIf(Range(nm), "*" & "Buick" & "*") Then 
Range(nm).EntireRow.Hidden = False
Next nm
End If

If TglDodge Then
For Each nm In Application.Names
If Application.CountIf(Range(nm), "*" & "Dodge" & "*") Then 
Range(nm).EntireRow.Hidden = False
Next nm
End If


End Sub


Sub CheckTegels()

If TglOpel Then
Call Tegels
Exit Sub
Else
If TglChevrolet Then
Call Tegels
Exit Sub
Else
 If TglFord Then
 Call Tegels
 Exit Sub
 Else
 If TglBuick Then
 Call Tegels
 Exit Sub
 Else
 If TglDodge Then
 Call Tegels
 Exit Sub
 Else

 Dim nm As Name

For Each nm In Application.Names
Range(nm).EntireRow.Hidden = False
Next nm


End If
End If
End If
End If
End If
End If
End If
End If
End Sub

Do you have any tips for speeding this process up?

As you can probably tell I am quite new to this. Any help would be greatly appreciated!

Some basic things. You need your code to be indented consistently. That way you can see the levels of your code. If you had that you would see that on the last line of SorterenOpdrachten is a Next without a For. This won't compile.

In Tegels you have missed your first End If. And the second End If. And your third, fourth and fifth.

You haven't defined tglopel or TglChevrolet