Ako vymazať alebo obnoviť niektoré pomenované oblasti

 

V realite sa stáva, že človek dostane pod ruku report, ktorý prešiel mnohými firmami – oddeleniami. Takéto reporty sa zvyknú správať tak trochu netradične. Značné problémy môžu spôsobovať nefungujúce definované oblasti (Defined Names). Pokiaľ sa pokúšate vytvoriť alebo obnoviť niektoré výpočtové operácie a chcete ich zjednodušiť pomocou definovaných oblastí, takýto pohľad do Name Manager-a nebude príjemný:

Pre zbavenie a možné nadefinovanie nových pomenovaných oblastí som vytvoril nasledujúce makro:

Sub CleanDefNames()
    Dim DefName As Name
    Dim UB
   

'For-cyklus vymaže všetky definované oblasti + On Error preskočí nejasnosti Excelu
    On Error Resume Next
    For Each DefName In ThisWorkbook.Names
        DefName.Delete
    Next DefName
    On Error GoTo 0
   
   'Definujeme počet riadkov novej definovanej oblasti
    UB = Sheets("DATA").Cells(Rows.Count, 1).End(xlUp).Row
   
    'Ak je posledný riadok rovný 1, potom definovanú oblasť nemá zmysel robiť - je prázdna
    If UB = 1 Then
        MsgBox "Hodnoty pre pomenovanú oblasť neexistujú", vbCritical
        Exit Sub
    End If
   
    'Vytvorí sa nová definovaná oblasť s názvom POKUS
    ActiveWorkbook.Names.Add Name:="POKUS", RefersToR1C1:="=DATA!R2C1:R" & UB & "C1"
   
    End Sub

Ak spustíte danú procedúru, v prípade, že v liste DATA v prvom stĺpci nemáte žiadnu hodnotu, objaví sa Vám nasledujúce okno:

Toto okno len upozorňuje, že sa pomenovaná oblasť POKUS nevytvorila, lebo stĺpec v liste DATA bol prázdny. V konečnom dôsledku týmto makrom si môžete neustále (po vlastnej úprave) aktualizovať pomenované oblasti a tým pádom budete mať po každom prečistení “čerstvé” výpočty.

Mimochodom, pomocou danej procedúry schudol môj report o viac než 0.5 MB, čo nie je na škodu veci. Celý tento proces si môžete pozrieť na nasledujúcom videu:

 

Refresh–Defined Names

Nabudúce si pripravíme niečo “SPECIAL” …. máte sa načo tešiť a nezabúdajte na Facebook fun-page!