четверг, 29 января 2015 г.

Как удалить скрытые имена в эксель-файле?

На работе мне приходится заполнять эксель-файл с индивидуальными целевыми показателями (используется для расчета премии). Шаблоны файлов поступили из центрального офиса. Ежемесячно я копирую последнюю вкладку и заполняю актуальной информацией. 

Всё бы ничего, но каждый раз при копировании выскакивает окно с уведомлением о том, что копируемые формулы содержат какие-то имена. И таких имён порядка 30 на странице. То есть, каждый раз копируя вкладку мне надо 30 раз нажать кнопку "Да".


Когда это случилось первый раз я просмотрел все ячейки, никаких имён не нашёл и... махнул на это рукой. Месяц спустя, когда снова копировал вкладку - выругался, 30 раз подтвердил и снова забыл на месяц. В общем, так прошло больше года. Однако, даже моё терпение заканчивается...

Вчера, в очередной раз столкнувшись с необходимостью нажимать кнопку "Да", залез в Гугл с запросом "удалить имена в эксель". Нашёл замечательную страничку, где прочитал, как можно просмотреть список имен (нажав кнопку «Все имена» в диалоговом окне, вызываемом из меню Вставка - Имя - Вставить...) и удалить ненужные

Сделал, удалил, но... диспетчер имён удаляет только видимые имена, а есть ещё и скрытые. 

Следующая ссылка привела на сайт Майкрософт (доступ только по учётной записи). Там выложен макрос, который позволяет удалять скрытые имена из эксель-файла. Копирую макрос здесь, чтобы на будущее не искать:
' Module to remove all hidden names on active workbook
Sub Remove_Hidden_Names()
' Dimension variables.
Dim xName As Variant
Dim Result As Variant
Dim Vis As Variant
' Loop once for each name in the workbook.
For Each xName In ActiveWorkbook.Names
'If a name is not visible (it is hidden)...
If xName.Visible = True Then
Vis = "Visible"
Else
Vis = "Hidden"
End If
' ...ask whether or not to delete the name.
Result = MsgBox(prompt:="Delete " & Vis & " Name " & _
Chr(10) & xName.Name & "?" & Chr(10) & _
"Which refers to: " & Chr(10) & xName.RefersTo, _
Buttons:=vbYesNo)
' If the result is true, then delete the name.
If Result = vbYes Then xName.Delete
' Loop to the next name.
Next xName
End Sub
Вот вся последовательность необходимых действий:


На вкладке "Вид" (1) нажимаем кнопку "Макросы" (2) - выскакивает окно. Вводим произвольное имя (3) - становится доступной кнопка "Создать" (4). Нажимаем...


Открывается окно редактора макросов с набранными несколькими строчками заготовки макроса (5). 



Копируем код макроса на место имеющегося текста и закрываем окно текущего макроса (6) и окно редактора макросов.


Снова Вид - Макросы и видим, что произвольное имя изменилось на Remove_Hidden_Names. Выбираем и нажимаем "Выполнить" (7)


Макрос запрашивает подтверждение на удаление каждого из имен, но это в последний раз. Так как кнопка "Yes" активна по умолчанию и если ни одно имя не нужно, то можно просто нажать и держать "Enter", пока все имена не будут удалены.

Всё. Теперь вкладку можно копировать без каких-либо дополнительных подтверждений.

6 комментариев:

  1. ОГРОМНЕЙШЕЕ спасибо. Очень помогло. Только я переделал, чтобы запросов подтверждения не было. Как оказалось не зря - у меня на листе было 1240 (примерно, я поставил счетчик) имен, ждать с подтверждениями - я бы не дождался.

    ОтветитьУдалить
    Ответы
    1. Так приятно читать такие комментарии! :) Очень рад, что помогло!

      Удалить
    2. подскажите пожалуйста, что вы исправили, у меня тоже очень много имен, даже диспетчер имен не открывается

      Удалить
    3. подскажите пожалуйста, что вы исправили, у меня тоже очень много имен, даже диспетчер имен не открывается

      Удалить
    4. Сразу оговорю, я не разбираюсь в языке макросов, чисто по ассоциации с другими языками программирования:

      ' Module to remove all hidden names on active workbook
      Sub Remove_Hidden_Names()
      ' Dimension variables.
      Dim xName As Variant
      Dim Result As Variant
      Dim Vis As Variant
      ' Loop once for each name in the workbook.
      For Each xName In ActiveWorkbook.Names
      'If a name is not visible (it is hidden)...
      If xName.Visible = True Then
      Vis = "Visible"
      Else
      Vis = "Hidden"
      End If
      xName.Delete
      ' Loop to the next name.
      Next xName
      End Sub

      Теоретически (проверить не на чём) должно будет удалить скрытые имена без запроса подтверждения

      Удалить
  2. Анонимный23 мая 2017 г., 8:32

    Спасибо! помогло!

    ОтветитьУдалить