Помогаем и разъясняем взаимодействие с ПО Ворд и Эксель на WordExcel.top
Excel зависает при удалении строк
Похоже, «поломалася» Проблема только с этим листом. При попытке копирования листа (формулы заменил на значения) в другую книгу вываливается в ошибку.
Вот оно. Excel ОЧЕНЬ не любит увлечения условным форматированием. Сильно разрастается объем файла, начинаются такие вот фокусы. На листе до сотни (не считал) форматов условного форматирования! Много дублей. Удалите лишние. или вообще все. Вкладка Главная-Условное_форматировние-Управление_правилами, здесь можно просмотреть заданные форматы и удалить лишние. Удалить все одним махом: Ctrl+G-Выделить-Условные_форматы, зайти Главная_УФ,-Удалить_правила_со_всего_листа
P.S. Интересно же, сколько. Недооценил…Посчитал количество условий УФ — 468!!! плюс-минус 5 шт.
Есть два ноутбука: тот же процессор, тот же объём ОЗУ (8 гб), на обоих ssd, на обоих одинаковая версия ОС и Офиса (2016). На одном таблица, при удалении 13 тысяч строк зависает намертво, на втором всё идеально. На проблемном ноуте полностью переустанавливалась ОС и Офис, проблема осталась. В безопасном режиме Excel’я проблема такая же. Одна и та же таблица, но на одном ноутбуке зависает таблица, на втором удаляешь-возвращаешь-удаляешь-возвращаешь — всё идеально.
Здравствуйте! Поднимал этот вопрос на другом форуме, но окончательного решения видимо не будет, рискну попробовать здесь:)
Вот этот пример с фильтром работает очень медленно в таблицах с большим числом строк, есть ли достойное альтернативное решение либо доработка существующего?:
Нужно удалять строки макросом по нескольким условиям без изменения структуры листа, в котором содержится таблица с большим количеством строк. В данном случае условием является «0» в шестом столбце и заданный цвет в том же столбце на всем листе, начиная с 22 строки. Подробнее и с примером во вложении.
[vba]
Код
Sub macros()
Rows(«22:22»).Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Range(Range(«a21:F21»), ActiveCell.SpecialCells(xlLastCell)).AutoFilter Selection.AutoFilter field:=6, Criteria1:=»=0,00″ Range(Range(«a21:F21»), ActiveCell.SpecialCells(xlLastCell)).SpecialCells(xlCellTypeVisible).EntireRow.Delete Application.ScreenUpdating = True End Sub
[/vba]
Спасибо заранее.
К сообщению приложен файл:
2075924.xls
(100.0 Kb)
Сообщение отредактировал Pelena — Четверг, 21.02.2013, 19:19
Ответить
AlexM
Дата: Четверг, 21.02.2013, 20:43 |
Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4257
Репутация: 1046 ±
Замечаний:
0% ± Excel 2003
Удалить строку, если в ячейке 6-го столбца (F) значение 0 (Ноль) А с цветом непонятно. Задан цвет серый, в таблице его не видно. Удалять нули серые или что угодно серого цвета???
Номер мобильного модема (без голосовой связи) 9269171249 МегаФон, Московский регион.
Сообщение отредактировал AlexM — Четверг, 21.02.2013, 20:46
Ответить
Гость
Дата: Четверг, 21.02.2013, 23:19 |
Сообщение № 3
To AlexM: Удалять что угодно серого цвета, в таблице это всякие строки маленькой высоты с надписями подраздел и просто пустые. Они удаляются при запуске второго макроса.
Ответить
AlexM
Дата: Пятница, 22.02.2013, 08:49 |
Сообщение № 4
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4257
Репутация: 1046 ±
Замечаний:
0% ± Excel 2003
Ваши макросы не не ботают в Excel2003 Первый не разбирался почему, а второй — в Excel2003 в автофильтре нет отбора по цвету ячейки.
Код, который работает, сравнить скорости я не смогу. [vba]
Код
Sub Macros3() Application.ScreenUpdating = False Dim i As Long, strS As String For i = 24 To Range(«F» & Rows.Count).End(xlUp).Row If (Range(«F» & i).Value <> «» And Range(«F» & i).Value = 0) Or Range(«F» & i).Interior.ColorIndex = 48 Then strS = strS & «,» & i & «:» & i End If Next: Range(Mid(strS, 2)).EntireRow.Delete Application.ScreenUpdating = True End Sub
[/vba]
Номер мобильного модема (без голосовой связи) 9269171249 МегаФон, Московский регион.
Ответить
Voh
Дата: Пятница, 22.02.2013, 12:54 |
Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Вставляю в мой файл с длинной таблицей, выдает: Run-time error ‘1004’ Method ‘Range’ of objekt ‘_Global’ failed ((( Выделяет в макросе строчку: Next: Range(Mid(strS, 2)).EntireRow.Delete
А так вроде уже почти получилось)
Ответить
AlexM
Дата: Пятница, 22.02.2013, 13:06 |
Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4257
Репутация: 1046 ±
Замечаний:
0% ± Excel 2003
А на вашем файле с короткой таблицей работает. Если строку [vba]
Код
Next: Range(Mid(strS, 2)).EntireRow.Delete
[/vba] изменить так [vba]
Код
Next: Range(Mid(strS, 2)).Delete
[/vba] то макрос быстрее работает
Номер мобильного модема (без голосовой связи) 9269171249 МегаФон, Московский регион.
Сообщение отредактировал AlexM — Пятница, 22.02.2013, 13:11
Ответить
ikki
Дата: Пятница, 22.02.2013, 13:11 |
Сообщение № 7
Группа: Друзья
Ранг: Старожил
Сообщений: 1906
Репутация: 504 ±
Замечаний:
0% ± Excel 2003, 2010
AlexM, у такого метода есть ограничения. и очень сильные. точнее — строка ограничена 255 символами. для тестирования: [vba]
Код
Sub t() Dim s$, t$, r As Range, i& For i = 1 To 100 Step 2 s = s & «,» & i & «:» & i Set r = Range(Mid(s, 2)) Debug.Print i, r.Areas.Count, Len(s) Next End Sub
[/vba]
помощь по Excel и VBA ikki@fxmail.ru, icq 592842413, skype alex.ikki
Сообщение отредактировал ikki — Пятница, 22.02.2013, 13:19
Ответить
AlexM
Дата: Пятница, 22.02.2013, 13:33 |
Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4257
Репутация: 1046 ±
Замечаний:
0% ± Excel 2003
Цитата (ikki)
у такого метода есть ограничения
Спасибо. Буду знать, что есть ограничения. Где почитать какие? Сейчас несколько статей посмотрел, а какие не узнал.
Цитата (ikki)
строка ограничена 255 символами.
Ок понял. Правим код. Перед Next вставить строку [vba]
Код
If Len(strS) > 240 Then Range(Mid(strS, 2)).Delete: strS = «»
[/vba] А строку с Next дополняем [vba]
Код
Next: If Len(strS) > 0 Then Range(Mid(strS, 2)).Delete
[/vba] Так должно работать.
Номер мобильного модема (без голосовой связи) 9269171249 МегаФон, Московский регион.
Сообщение отредактировал AlexM — Пятница, 22.02.2013, 13:52
Ответить
Voh
Дата: Пятница, 22.02.2013, 21:28 |
Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Макрос примерно 30-40 секунд вычисляет, потом:
Run-time error ’13’: Type mismatch
На этот раз выделяет желтым: If (Range(«F» & i).Value <> «» And Range(«F» & i).Value = 0) Or Range(«F» & i).Interior.ColorIndex = 48 Then
причем в файле частично удаляет строки, пока не понял по какому принципу..
Ответить
AlexM
Дата: Пятница, 22.02.2013, 23:05 |
Сообщение № 10
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4257
Репутация: 1046 ±
Замечаний:
0% ± Excel 2003
Замените строку [vba]
Код
For i = 24 To Range(«F» & Rows.Count).End(xlUp).Row
[/vba] на [vba]
Код
For i = Range(«F» & Rows.Count).End(xlUp).Row To 24 Step -1
[/vba]
Номер мобильного модема (без голосовой связи) 9269171249 МегаФон, Московский регион.
Ответить
Voh
Дата: Пятница, 22.02.2013, 23:35 |
Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Угу, теперь цикл доходит до конца, правда сортирует только по 0, а цвета игнорирует. Ну с этим я смогу справиться, например спрячу везде нули, а вот проблема в том, что макрос выполняется более 2х минут у меня, а на слабеньких компах будет еще медленнее.. Т.е. это и является сутью моей проблемы: не получается добиться того, чтобы в очень больших таблицах удалялись строки по условию также быстро, как и в маленьких..
Ответить
AlexM
Дата: Суббота, 23.02.2013, 08:41 |
Сообщение № 12
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4257
Репутация: 1046 ±
Замечаний:
0% ± Excel 2003
В файле примере не удаляет цветные строки? В примере таблицы два серых цвета. Макрос удаляет строки с темно серые.
Номер мобильного модема (без голосовой связи) 9269171249 МегаФон, Московский регион.
Ответить
Voh
Дата: Суббота, 23.02.2013, 14:08 |
Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Нет, цветные строки не удаляются в файле-примере. Я специально заполнял даже их предварительно 48 цветом, как в Вашем коде…(
Ответить
Hugo
Дата: Суббота, 23.02.2013, 16:32 |
Сообщение № 14
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3140
Репутация: 670 ±
Замечаний:
0% ± 2010, теперь уже с PQ
Если бы не нужно было анализировать цвет — можно на любую таблицу сделать удаление очень бымтро. Ну а если нужен анализ цвета — тогда делайте используя фильтр по цвету под 2007. На каком Экселе вообще будет выполняться работа?
На 2007. Я думаю, сделаю удаление по цвету вторым макросом просто, раз в одном скомпоновать сложно будет.
Ответить
Michael_S
Дата: Суббота, 23.02.2013, 20:24 |
Сообщение № 16
Группа: Друзья
Ранг: Старожил
Сообщений: 2012
Репутация: 373 ±
Замечаний:
0% ± Excel2016
Цитата (Voh)
Нужно удалять строки макросом по нескольким условиям без изменения структуры листа,
Цитата (Гость)
Удалять что угодно серого цвета, в таблице это всякие строки маленькой высотыс надписями подраздел и просто пустые.
Два несовместимых условия: ели удалять все серые сроки в вашем примере — группировка исчезнет. Вернее, останется только один уровень.
Сообщение отредактировал Michael_S — Суббота, 23.02.2013, 20:25
Ответить
shanemac51
Дата: Суббота, 23.02.2013, 21:12 |
Сообщение № 17
Группа: Проверенные
Ранг: Новичок
Сообщений: 33
Репутация: 4 ±
Замечаний:
0% ±
[vba]
Код
Sub Macros4() »обычно удаление идет с хвоста Dim tm! tm = Timer Application.ScreenUpdating = False Dim i As Long, strS As String Dim i2 i = Range(«F» & Rows.Count).End(xlUp).Row Debug.Print i Do While i > 24 If Cells(i, 6) = 0 Or Cells(i, 6).Interior.ColorIndex = 48 Then Rows(i).Delete End If i = i — 1 Loop Application.ScreenUpdating = True Debug.Print «Строки удалены за » & Timer — tm & » сек» End Sub
[/vba]
Ответить
Voh
Дата: Воскресенье, 24.02.2013, 10:44 |
Сообщение № 18
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Цитата (Michael_S)
Два несовместимых условия
Да, Вы правы, но это неизбежно и, в качестве исключения, допустимо.
Ответить
Voh
Дата: Воскресенье, 24.02.2013, 11:03 |
Сообщение № 19
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Цитата (shanemac51)
Sub Macros4
10 минут..(
Ответить
Michael_S
Дата: Воскресенье, 24.02.2013, 12:17 |
Сообщение № 20
Группа: Друзья
Ранг: Старожил
Сообщений: 2012
Репутация: 373 ±
Замечаний:
0% ± Excel2016
10 минут — это сколько строк у вас в таблице? и сколько удаляется?
