Нужно уходить от таких размеров. Excel не любит большого веса, от ожирения у него начинаются всяческие приступы

Запомнить адреса и почистить одним движением.

Общие рекомендации: отключать автопересчет листа, обновление экрана, события листа...  Обращение к ячейкам, листу - медленный процесс. Минимизировать, обрабатывать данные в массивах (в памяти).

Плюс избавляемся от использования метода .Select и, по мере возможности, от прямых манипуляций с диапазонами (вместо этого работаем с данными в памяти через массивы или словарь)  При отключении перерисовки экрана и пересчета формул (см #4), очистка 50.000 ячеек даже по одной должна быть практически молниеносной

урааааа!все так быстро работает!!
всем спасибо!и добрых снов!

Мои 5 оптимизационных копеек

Оптимизация
1) Избегать формул массивов (по возможности)
2) Избегать волатильных формул (по возможности) (условное форматирование, как пример волатильности)
3) Использовать структурированные таблицы и именнованные диапазоны
4) заменять неиспользуемые формулы значениеями
5) Хранить все данные для расчета на одном листе (по возможности)
6) Избегать в формулах ссылки на целые столбцы
7) использовать =IFERROR вместо IF + ISERR/ISERROR
8) использовать MAX(A1;0) вместо IF(A1>0;A1;0)
9) использовать INDEX+MATCH вместо VLOOKUP
10) использовать -- для конвертации лог.значение в 0/1
11) использовать A1*0.1 чем A1/10
VBA
12) Используй оператор if else, вместо IIF
13) Используй Long т.е. Long лучше Вуte, Integer, конечно, variant
14) Применяй if else, чем switsh; shoose
15) Лучше проверяй строку так len(s)=0, чем s = “”
16) Лучше инициализируй строку так s =vbnullstring, чем s =""
17) Если возможно пиши так s = "АВ", чем s ="А" & "В"
18) Вставляй строку в текст так Мid$(а,3,4)="like". чем s = left$(8,2) & "like" & mid(з,7)
19) Сравнивай строки так if strcomp(s1,s2,vbtextcompare) =0, чем if ucase(s1)=ucase(s2)
20) Лучше используй $-функцию, т.е. left$ лучше left.
21) Цикл for …next. работает быстрее do...lоор
22) Точнее объявляй объекты: as commandbutton лучше as control, еще лучше as object
23) Цикл for Еасh..next лучше, чем цикл for…next семейств объектов
24) Цикл for ...next лучше, чем цикл fог Еасh...next: для массивов.
25) Использовать оператор With при обращении к элементам сходной иерархической модели
Для общих (расшаренных) книг
26) Удалить пользовательские представления Вид-Представления-Удалить
27) Снять пометки с Параметров печати и Фильтров , так Рецензировани-Доступ к книге-Параметры печати и Фильтры
28) Периодически отключать общий доступ, сохранять книгу, сключать общий доступ и снова сохранять
29) Удостовериться, что книга открывается в режиме просмотра Вид-Обычный (для того, чтобы избежать обращения к сетевому принтеру с соответствующими временными последствиями)
Для уменьшения размера книги MSE (актуально для книг в которых часто пишется и удаляется инфа)
30) Перелить одну книгу в другую путем копирования не листа, а содержимого в новую внигу со вставкой сначала значения, потом формулы, потом форматы и макросы через испорт экспорт перенести в новую книгу
31 ) удалить избыточное форматирование

имелось в виду лучше использовать commandbutton, чем более глобальные объекты...коряво написал...

e_artem, если позволите, в вашем разделе Оптимизация есть весьма сомнительные/спорные обобщения

1) избегать формул массива потому, что некоторые могут подвесить расчеты от непонимания того, как они действуют, - все равно, что избегать VBA потому, что можно по незнанию попасть в бесконечный цикл. Вопреки поверьям, формулы массива не медленнее, чем отдельные формулы их составляющие если сумма операций одна и та же. Формулы массива менее эффективны лишь тогда, когда алгоритм не оптимален: а. повторные расчеты, которых можно избежать, вынеся на лист как отдельные формулы, б. как частный случай, бинарные условные расчеты типа СУММПРОИЗВ(...), где нет возможности прекратить расчеты после нахождения искомого или где не был ограничен использованный диапазон. Точно также не все формулы массива длинные и непонятные

2) волатильность - не абсолютное зло, ее следует избегать в специфическом случае, когда сумма времен пересчета всех зависимых от нее формул достигает уровня создающего неудобства в работе с файлом, например 1 секунда и более (но последнее субъективно). Отказ от волатильных функций в других случаях может означать ненужные ограничения. Для примера, волатильны все функции времени и случайных чисел, а также СМЕЩ, ДВССЫЛ, ЯЧЕЙКА, ИНФОРМ и некоторые частные случаи неволатильных функций.

3) совсем непонятно избегание имен единственный недостаток я могу себе представить - это пересчет каждого упоминания имени в случае если зависимая формула пересчитывается, но это может быть проблемой лишь в случае "тяжелых" расчетов (например точного поиска или поиска по условиям) хранимых в имени. В этом смысле это то же, что и мой подпункт а. в пункте 1) этого списка.

6) злоупотреблять конечно не стоит, но на всякий случай, использование целых столбцов в функциях точного поиска и функции ИНДЕКС абсолютно не влияет на эффективность формулы. В случае функций неточного поиска, влияние нематериально даже при множестве повторов ввиду бинарного алгоритма. Реально известная проблема с этим - опять таки бинарные условные формулы типа СУММПРОИЗВ(...)

По формулам массива еще момент: например, ТРАНСП, которая вводится разом во все ячейки будет пересчитываться быстрее, чем то же транспонирование через ИНДЕКС, т.к. рассчитывается один раз, а ИНДЕКС столько раз, сколько ячеек задействовано.
И во многих случаях формулы массива, введенные разом в диапазон ячеек быстрее будут работать, чем ввод формулы массива в одну ячейку и "тиражирование" её на множество ячеек.
Но опять же: все всегда зависит от конкретной ситуации и порой там, где одно быстрее - другое будет медленнее.

Разница в скорости между ИНДЕКС/ПОИСКПОЗ и ВПР нематериальна даже на очень больших диапазонах. Тем не менее гибкость - главное и важное преимущество ИНДЕКС/ПОИСКПОЗ.

Серьезная оптимизация скорости возникает при замене точного поиска на неточный по совету Чарльза Уильямса (если данные можно отсортировать) или при выносе ПОИСКПОЗ в доп. столбец (если нужно получить результат из множества столбцов базы). Но даже в последнем случае у ВПР есть ответ - использование массива в 3-ем параметре.

Интересная информация. Т.е. отсортировав список и применив ПОИСКПОЗ с неточным поиском, получим ускорение вычислений?

Вить, так это же логично. ПОИСКПОЗ просматривает отсортированный массив и в зависимости от типа соответствия может реально и точно сравнить данные раньше и позже с текущим и понять - имеет ли смысл идти дальше или уже достигнут предел максимально точного соответствия.

В чем разница? И при точном, и при неточном соответствии нашли значение (в сортированном массиве) - остановились. Тут, наверное, разница в логике поиска?

Да, неточный - бинарный, точный - поячеечно. Бинарный значит максимум 19 проверок в миллионе строк.

И от меня спасибо.
Писал же не раз о бинарном, а я ушами прохлопал.

Немного дополнил макрос Roman Shaleyko, из ответа № 3, добавив функционал из темы Макрос отключения/изменения характеристик вкладки Вид (Обычный режим просмотра книги, Сетка, Строка формул, Заголовки) (спасибо форумчанам МатросНаЗебре, Конь)
Итак, сами макросы


Может быть кому-то пригодится.
П.С. данный сборник не является какой-либо панацеей и не является готовым решением всех проблем. В данном сборнике, собраны всякие разные настройки в один код - включает и выключает. Особо подчеркну: не все на быстродействие влияют, но вдруг кому надо будет - все в одном месте. Используя данный сборник, пользователь принимает на себя все риски связанные с его использованием и результат работы сборника не всегда может соответствовать ожиданиям пользователя к процессу/результату его работы.