Excel тормозит. Что делать?

Обратился к нам клиент с вопросом: тормозит Excel, причем именно на одном конкретном листе. Все остальные листы в той же книге работают нормально. Анализ (и Google) дал  — правда к тому времени мы уже догадались грохнуть огромный файл drawings внутри архива xlsx, но макрос не менее ценен! Суть проблемы в огромном количестве пустых объектов типа Shape. Нужно их удалить и будет всем счастье!

 

Sub DeleteAllTextBox()
Dim oSh As Shape
For Each oSh In ActiveSheet.Shapes
oSh.Delete
Next oSh
End Sub

Мой знакомый прислал мне Excel-файл содержащий порядка 200 строк без формул и связей, и весящий около 28МВ! Для перехода курсора из ячейки в ячейку требовалось несколько секунд. Первое, что я сделал, это удалил всё форматирование, очистил все пустые ячейки. Не сильно помогло. Удалил все данные. Ситуация чуть улучшилась, но четко было видно, что файл «тормозит». Задал вопрос в Google – о чудо! – нашел ответ.

Наверное это побочный эффект копирования. Похожая вещь возникает при вставке данных целыми листами или столбцами/строками из файлов, созданных 1С и подобных (ERP) систем: они генерируют в файл «пустые» ячейки (забитые пробелами) и объекты типа Надпись. В итоге со временем файл становится очень большим.
В Excel2003: меню Правка → Перейти → Выделить → Объекты.
В Excel2007: вкладка Главная → Найти и выделить → Выделение группы ячеек → Объекты.
Весь процесс сопровождался долгими лагами, но в итоге выделилась какая-то непонятная ерунда, которую я удалил, после чего торможение пропало.

К сожалению, в моем файле оказалось почти 14 000! :( . Чтобы увидеть все объекты, пройдите по меню: вкладка Главная → Найти и выделить → Область выделения:

Часть объектов можно увидеть справа на экране; один объект под номером 13 697 выделен в списке и на экране:

При попытке выделить все объекты Excel зависал (через 30 минут надоело, снял задачу).

Второе обращение к Google «навело» на макрос.:

Sub DeleteAllTextBox()

Dim oSh As Shape

For Each oSh In ActiveSheet.Shapes

oSh.Delete

Next oSh

End Sub

То есть, не выделять все объекты сразу, а запустить макрос, который будет находить объекты по одному и удалять. В файле макрос справился с уничтожением 14 000 объектов за 5 минут! :)

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *