Bereik(Naam) verwijderen met cijfers als laatste karakters

[ErikdV]

Ik heb een aantal tabbladen die een nummer als naam hebben. (bijv. 1 tot 15)
Het kan voorkomen dat een van de tabbladen ontbreekt (bijv. 3)
Hiervoor heb ik een macro, die de ontbrekende tabbladen in een array TabbladNB(AantalNB) zet.
Als een tabblad niet bestaat wil ik de bereiken(namen) verwijderen die bij het tabblad horen.
De namen zijn zodanig gedefinieerd, dat de laatste karakters altijd hetzelfde zijn als de naam van het bijbehorende tabblad.
Zo hoort bijv. Erik3 bij tabblad 3 en erik13 bij tabblad 13

Ik kan ervoor kiezen om 'Refersto' alle ongeldige verwiijzingen te verwijderen, maar ik wil geen bereiken verwijderen met een naam als 'Jan_totaal',
maar alleen die verwijzen naar een genummerd tabblad.

Onderstaande code zou dit moeten doen.
Ik bepaal eerst het aantal cijfers(karakters) van het nummer en vervolgens verwijder ik de namen die overeenkomen met een niet bestaand tabblad.
Het gaat niet goed als zowel 'Erik3'als 'Erik13' als naam bestaat, terwijl alleen het tabblad 3 niet bestaat.
Ik wil dan niet 'Erik13'verwijderen, maar wel 'Erik3'
Het script wil beide verwijderen.

Hoe kan ik dit oplossen?

For nb = 1 To aantalNB
Shtname = TabbladNB(nb) 'De naam van het genummerde tabblad uit de array halen
If Shtname < 10 Then p = 1 '1 tot 9 tabbladen
If Shtname > 9 And s < 100 Then p = 2 '10 tabbladen of meer -> p=2
If Shtname > 99 Then p = 3 '100 tabbladen of meer

For Each n In ThisWorkbook.Names
'If InStr(1, n.RefersTo, "#REF") > 0 Then n.Delete
If Right(n.Name, p) = Shtname Then
If Len(Shtname) = p Then Debug.Print "naam: "; n.Name; ": p "; p; "Shtname: "; Shtname
'If Len(Shtname) = p Then n.Delete
End If
Next
Next

 

[HSV]

Als een blad niet meer bestaat, staat er in de verwijzing naar het bereik in de gedefinieerde naam "#VERW!"
Dan is het niet zo moeilijk om de namen te verwijderen.

Sub hsv()
Dim nm As Name
 For Each nm In Application.Names
  If InStr(1, nm, "#") > 0 Then
   nm.Delete
  End If
 Next
End Sub

 

[ErikdV]

Ik weet dat je in een keer alle namen zonder geldige verwijzing kan verwijderen.
Je ziet dat ook aan de code die ik als commentaar heb weergegeven in rood.

Als ik echter alle namen zonder geldige verwijzing verwijder, worden ook namen verwijderd die ik op een andere plaats misschien nog gebruik.
Als ik dan problemen tegenkom in mijn werkmap kan ik niet goed nagaan waar deze problemen vandaan komen als ik de namen niet zie in de lijst.
Bovendien wil ik de code ook gaan gebruiken voor het herstellen van een weliswaar geldige, maar toch niet correcte verwijzing.

 

[HSV]

Als je tabbladen een nummer als naam hebben.

Test het maar eens.

Sub hsv()
Dim Regex As Object, objM As Object, nm As Name
Set Regex = CreateObject("VbScript.RegExp")
With Regex
  For Each nm In Names
      .Pattern = "([A-Za-z]+|[0-9]+)"
      .Global = True
    Set objM = .Execute(nm.Name)
         If IsError(Evaluate(objM(1) & "!A1")) Then nm.Delete
    Next nm
 End With
End Sub

 

[ErikdV]

Ik ga het anders doen

Ik heb een andere manier gevonden om het probleem op te lossen:

Ik zet de naam in variabele 1 en het nummer in variabele 2.
voorbeeld: variabele 1 = "Erik" en variabele 2 = 13 (nummer tabblad)
variabele 3 = dan variabele 1 & variabele 2 (Erik13)
Zo kan ik op de soort naam, het tabtabnummer en de naam (soort + nummer) testen.