Ggthjy

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