Макрос удалить листы в эксель по условию в ячейке
Предположу, что почти каждый сталкивался с ситуацией, когда необходимо удалить только определенные строки: имеется большая таблица и необходимо удалить из неё только те строки, которые содержат какое-то слово (цифру, фразу). Для выполнения подобной задачи можно воспользоваться несколькими способами.
Способ первый:
Использовать встроенное средство Excel - фильтр. Сначала его необходимо "установить" на листе:
- Выделяем таблицу с данными, включая заголовки. Если их нет - то выделяем с самой первой строки таблицы, в которой необходимо удалить данные
- устанавливаем фильтр:
- для Excel 2003 : Данные-Фильтр-Автофильтр
- для Excel 2007-2010 : вкладка Данные (Data) -Фильтр (Filter)(или вкладка Главная (Home) -Сортировка и фильтр (Sort&Filter) -Фильтр (Filter) )
Теперь выбираем условие для фильтра:
- в Excel 2003 надо выбрать Условие и в появившейся форме выбрать непосредственно условие("равно", "содержит", "начинается с" и т.д.), а напротив значение в соответствии с условием.
- Для 2007-2010 Excel нужно выбрать Текстовые фильтры (Text Filters) и либо сразу выбрать одно из предлагаемых условий, либо нажать Настраиваемый фильтр (Custom Filter) и ввести значения для отбора в форме
После этого удалить отфильтрованные строки. В 2007 Excel могут возникнуть проблемы с удалением отфильтрованных строк, поэтому рекомендую сначала так же прочитать статью: Excel удаляет вместо отфильтрованных строк - все?! Как избежать.
Способ второй:
применить код VBA, который потребует только указания значения, которое необходимо найти в строке и номер столбца, в котором искать значение.Если значение sSubStr не будет указано, то будут удалены строки, ячейки указанного столбца которых, пустые.
Данный код необходимо поместить в стандартный модуль. Вызвать с листа его можно нажатием клавиш Alt + F8 , после чего выбрать Del_SubStr и нажать Выполнить. Если в данном коде в строке
If -(InStr(Cells(li, 1), sSubStr) > 0) = lMet Then
вместо = lMet указать <> lMet , то удаляться будут строки, не содержащие указанное для поиска значение. Иногда тоже удобно.
Но. Данный код просматривает строки на предмет частичного совпадения указанного значения. Например, если Вы укажете текст для поиска "отчет", то будут удалены все строки, в которых встречается это слово("квартальный отчет", "отчет за месяц" и т.д.). Это не всегда нужно. Поэтому ниже приведен код, который будет удалять только строки, указанные ячейки которых равны конкретно указанному значению:Здесь так же, как и в случае с предыдущим кодом можно заменить оператор сравнения( Cells(li, lCol) = sSubStr ) с равно на неравенство( Cells(li, lCol) <> sSubStr ) и тогда удаляться будут строки, значения ячеек которых не равно указанному.
УДАЛЕНИЕ СТРОК НА ОСНОВАНИИ СПИСКА ЗНАЧЕНИЙ(МНОЖЕСТВЕННЫЕ КРИТЕРИИ)
Иногда бывают ситуации, когда необходимо удалить строки не по одному значению, а по нескольким. Например, если строка содержит или Итог или Отчет. Ниже приведен код, при помощи которого можно удалить строки, указав в качестве критерия диапазон значений.
Значения, которые необходимо найти и удалить перечисляются на листе с именем "Лист2". Т.е. указав на "Лист2" в столбце А(начиная с первой строки) несколько значений - они все будут удалены. Если лист называется иначе(скажем "Соответствия") в коде необходимо будет "Лист2" заменить на "Соответствия". Удаление строк происходит на активном в момент запуска кода листе. Это значит, что перед запуском кода надо перейти на тот лист, строки в котором необходимо удалить.Sub Del_Array_SubStr() Dim sSubStr As String 'искомое слово или фраза Dim lCol As Long 'номер столбца с просматриваемыми значениями Dim lLastRow As Long, li As Long Dim avArr, lr As Long Dim arr lCol = Val(InputBox("Укажите номер столбца, в котором искать указанное значение", "www.excel-vba.ru", 1)) If lCol = 0 Then Exit Sub Application.ScreenUpdating = 0 lLastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count 'заносим в массив значения листа, в котором необходимо удалить строки arr = Cells(1, lCol).Resize(lLastRow).Value 'Получаем с Лист2 значения, которые надо удалить в активном листе With Sheets("Лист2") 'Имя листа с диапазоном значений на удаление avArr = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp)) End With 'удаляем Dim rr As Range For lr = 1 To UBound(avArr, 1) sSubStr = avArr(lr, 1) For li = 1 To lLastRow 'цикл с первой строки до конца If CStr(arr(li, 1)) = sSubStr Then If rr Is Nothing Then Set rr = Cells(li, 1) Else Set rr = Union(rr, Cells(li, 1)) End If End If DoEvents Next li DoEvents Next lr If Not rr Is Nothing Then rr.EntireRow.Delete Application.ScreenUpdating = 1 End Sub
Чтобы код выше удалял строки не по точному совпадению слов, а по частичному(например, в ячейке записано "Привет, как дела?", а в списке есть слово "привет" - надо удалить, т.к. есть слово "привет"), то надо строку:
If CStr(arr(li, 1)) = sSubStr Then
заменить на такую:
If InStr(1, arr(li, 1), sSubStr, 1) > 0 Then
УДАЛЕНИЕ ИЗ ЛИСТА СТРОК, КОТОРЫХ НЕТ В СПИСКЕ ЗНАЧЕНИЙ(МНОЖЕСТВЕННЫЕ КРИТЕРИИ)
Т.к. в последнее время стало поступать все больше и больше вопросов как не удалять значения по списку, а наоборот - оставить в таблице только те значения, которые перечислены в списке - решил дополнить статью и таким кодом.
Значения, которые необходимо оставить перечисляются на листе с именем "Лист2". Т.е. указав на "Лист2" в столбце А(начиная с первой строки) несколько значений - после работы кода на листе будут оставлены только те строки, в которых присутствует хоть одно из перечисленных в списке значений. Если лист называется иначе(скажем "Соответствия") в коде необходимо будет "Лист2" заменить на "Соответствия". Удаление строк происходит на активном в момент запуска кода листе. Это значит, что перед запуском кода надо перейти на тот лист, строки в котором необходимо удалить.
В отличие от приведенных выше кодов, данный код ориентирован на то, что значения в списке указаны не полностью. Т.е. если необходимо оставить только те ячейки, в которых встречается слово "активы", то в списке надо указать только это слово. В этом случае если в ячейке будет записана фраза "Нематериальные активы" или "Активы сторонние" - эти ячейки не будут удалены, т.к. в них встречается слово "активы". Регистр букв при этом неважен.'процедура оставляет в листе только те значения, которые перечислены в списке Sub LeaveOnlyFoundInArray() Dim sSubStr As String 'искомое слово или фраза Dim lCol As Long 'номер столбца с просматриваемыми значениями Dim lLastRow As Long, li As Long Dim avArr, lr As Long Dim arr Dim IsFind As Boolean lCol = Val(InputBox("Укажите номер столбца, в котором искать указанное значение", "www.excel-vba.ru", 1)) If lCol = 0 Then Exit Sub Application.ScreenUpdating = 0 lLastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count 'заносим в массив значения листа, в котором необходимо удалить строки arr = Cells(1, lCol).Resize(lLastRow).Value 'Получаем с Лист2 значения, которые надо удалить в активном листе With Sheets("Лист2") 'Имя листа с диапазоном значений на удаление avArr = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp)) End With 'удаляем Dim rr As Range For li = 1 To lLastRow 'цикл с первой строки таблицы до конца IsFind = False For lr = 1 To UBound(avArr, 1) 'цикл по списку значений на удаление sSubStr = avArr(lr, 1) If InStr(1, arr(li, 1), sSubStr, 1) > 0 Then IsFind = True End If DoEvents Next lr 'если значение таблицы не найдено в списке - удаляем строку If Not IsFind Then If rr Is Nothing Then Set rr = Cells(li, 1) Else Set rr = Union(rr, Cells(li, 1)) End If End If DoEvents Next li If Not rr Is Nothing Then rr.EntireRow.Delete Application.ScreenUpdating = 1 End Sub
Чтобы код выше сравнивал значения таблицы со значениями списка по точному совпадению слов, а не по частичному, то надо строку:
у кого-нибудь есть готовый макрос для удаления листов по числовому критерию из ячейки?
Причем данный макрос запускаться с открытием книги и в конце автоматически сохраняет данную книгу.
Я не знаю как прописать в макросе условие на ячейку А1 и критерии по удалениям.
Макрос удаления текста выделенной области, удовлетворяющего заданному условию
Приветствую, знатоки VBA. Очень нужна ваша помощь. Требуется написать макрос MTextClear, который.Поиск по заданному критерию
Доброго времени суток! Пробовал сделать следующее: Разработайте программу, которая позволяет.Поиск в строке 1 по заданному критерию
помогите автоматизировать работу. мне надо выполнить поиск в строке 1 по заданному критерию.Макрос Сортировки по сложному критерию
Здравствуйте. Кому не сложно и есть время, помогите ((( Необходимо сделать сортировку таблицы.Спасибо конечно) Но я не понимаю как это вставить в макрос и куда именно + как автоматизировать его на открытие книги и на автосохранение.
Вобщем я пытался след. образом, через запись макроса:Вопрос ведь был в чём? -
"Я не знаю как прописать в макросе условие на ячейку А1 и критерии по удалениям."
Вот это я и написал - и проверка, и критерий.
Вам осталось дописать то, что знаете - удаление нужных листов в каждом из условий проверки.неа, вопрос был:
у кого-нибудь есть готовый макрос для удаления листов по числовому критерию из ячейки?а остальное - констатацией факта.
У меня такого нет
А по факту - лень файл с такими листами делать, чтоб код проверить.
И ещё - сохранить в xlsm я не могу.Я немного обыграл ситуацию. Короче, у меня на каждом листе, в каждой троке в любом случае есть текст/цифра "1" ( в дате и т.п.).
Так вот, можно не удалять лист, а удалять строки на листе, которые содержат киртерий "1" - т.е. лист все равно будет пустым. Добавил туда, как Вы написали, критерий из ячейки - работает!
Также добавил в макрос автосохранение.
Вобщем все работает, кроме автосохранения. Т.е. сохраняется не со своим именем, а прописанным:Вопрос: Как "1111.хлсм" прописать, чтобы всегда было тоже имя файла, которое и есть у файла, в независимости куда это макрос помещен. Т.е. как прописать что-то типа:
И какой код прописать в начале для автозапуска макроса при открытии?
На данный момент макрос выглядит так:
совершенно лишнее, вполне можно заменить на одну строку
не работает.
В ячейке стоит 70.
Критерий в макросе я поставил - [a1]удаляются все строки.
Спасибо. работает идеально, но с одним но:
перед удалением спрашивает подтверждение на удаление листов. Как сделать, что это было автоматически?
что во второй части макроса скорректировать?Огромное спасибо всем.
Если бы это все работало бы ещё и с выключенными макросами - было бы вообще идеально)Можно с выключенными - поместите код от Султанов в модуль в персональную книгу или надстройку.
Но код возможно нужно чуть скорректировать.
А может и так сработает - нужно проверять.
Вызывать выполнение можно по Alt+F8 или кнопкой на панели (сделать).
Срабатывание при открытии книги тоже думаю можно сделать - обрабатывать событие открытия любой книги, искать метку.
Но не гарантирую - пробовать нужно.Я имею ввиду немного другое и немного под другую задачу, но принцип работы тотже:
например, есть база уплаты/не уплаты налогов или др. черная бухгалтерия, учет в которой отражает реальные ден.потоки и есть официальная 1С или т.п. И вот приходит налоговая и с помошниками из др. минестерств и изымают компы, проверяют. В 1С данные красивые, а файл с черн.бух-ей не открывается - при открытии все стирается: стоит критерий "если открыть базу через некоторое время= удалить все нафиг " (текущая дата-дата сохранения>=3 часам, например). И автоматом автосохранение (как у меня).
Т.о. файл открыть через 3 часа после последнего сохранения возможно только отключив данный макрос.
Ну вобщем, имею ввиду, что-нить типа этого
Ни одного Аль Капоне потом не поймают
Я так понимаю, что нужна цифровая подпись под этот макрос (я уже бегло прочитал про автомакросыУдалить все листы, кроме заданных
доброго дня всем Есть книга в которую юзеры добавляют Листы какие им вздумается Какой макрос.(excel_2010_VBA) Удалить все листы кроме активного
Здравствуйте. Нашел макрос но у меня что-то не хочет работать, помогите человеку далекому от.Записать значения во все листы , кроме
Всем привет, У меня имеется лист в файле Экселя с названием "Источник" В этот лист будут.- Если у листа, который Вы планируете удалить, значение свойства Visible установлено как xlVeryHidden/xlSheetVeryHidden, то попытка его удаления приведёт к ошибке, которую можно избежать, если перед удалением сделать лист видимым, т.е. установить значение свойства Visible = True/xlSheetVisible
- После удаления листа, в рабочей книге должен остаться, как минимум, один видимый лист - Нельзя удалить лист, если рабочая книга является общей
- Нельзя удалить лист, если структура рабочей книги защищенаДобавлено через 7 минут
это то что по правилам,
а ксательно вашего примера вы подсчитываете количество листов, а удалять хотите по его индексуНужно перевернуть цикл. Ошибка возникает потому, что когда Вы удаляете лист № 2, порядковые номера листов смещаются. Когда цикл доходит до конца, то листа с номером Worksheets.Count (на тот момент) уже не существует.
- Если у листа, который Вы планируете удалить, значение свойства Visible установлено как xlVeryHidden/xlSheetVeryHidden, то попытка его удаления приведёт к ошибке, которую можно избежать, если перед удалением сделать лист видимым, т.е. установить значение свойства Visible = True/xlSheetVisible
- После удаления листа, в рабочей книге должен остаться, как минимум, один видимый лист - Нельзя удалить лист, если рабочая книга является общей
- Нельзя удалить лист, если структура рабочей книги защищенаНи один из листов не скрывался, то есть должны быть видимы по умолчанию. Книга не защищена - все по умолчанию.
А сам макрос делался для того чтобы удалить все листы кроме первого, то есть в любом случае, как минимум один лист остается.А разве индекс устанавливается не по порядку листов? То есть, подсчет количества листов должен быть аналогичен индексу последнего листа.
Цикл на удаления по задумке должен удалять все листы с индексом от второго до последнего по порядку из существующих.Добавлено через 3 минуты
Нужно перевернуть цикл. Ошибка возникает потому, что когда Вы удаляете лист № 2, порядковые номера листов смещаются. Когда цикл доходит до конца, то листа с номером Worksheets.Count (на тот момент) уже не существует.
Предположу, что почти каждый сталкивался с ситуацией, когда необходимо удалить только определенные строки: имеется большая таблица и необходимо удалить из неё только те строки, которые содержат какое-то слово (цифру, фразу). Для выполнения подобной задачи можно воспользоваться несколькими способами.
Способ первый:
Использовать встроенное средство Excel - фильтр. Сначала его необходимо "установить" на листе:- Выделяем таблицу с данными, включая заголовки. Если их нет - то выделяем с самой первой строки таблицы, в которой необходимо удалить данные
- устанавливаем фильтр:
- для Excel 2003 : Данные-Фильтр-Автофильтр
- для Excel 2007-2010 : вкладка Данные (Data) -Фильтр (Filter)(или вкладка Главная (Home) -Сортировка и фильтр (Sort&Filter) -Фильтр (Filter) )
Теперь выбираем условие для фильтра:
- в Excel 2003 надо выбрать Условие и в появившейся форме выбрать непосредственно условие("равно", "содержит", "начинается с" и т.д.), а напротив значение в соответствии с условием.
- Для 2007-2010 Excel нужно выбрать Текстовые фильтры (Text Filters) и либо сразу выбрать одно из предлагаемых условий, либо нажать Настраиваемый фильтр (Custom Filter) и ввести значения для отбора в форме
После этого удалить отфильтрованные строки. В 2007 Excel могут возникнуть проблемы с удалением отфильтрованных строк, поэтому рекомендую сначала так же прочитать статью: Excel удаляет вместо отфильтрованных строк - все?! Как избежать.
Способ второй:
применить код VBA, который потребует только указания значения, которое необходимо найти в строке и номер столбца, в котором искать значение.Если значение sSubStr не будет указано, то будут удалены строки, ячейки указанного столбца которых, пустые.
Данный код необходимо поместить в стандартный модуль. Вызвать с листа его можно нажатием клавиш Alt + F8 , после чего выбрать Del_SubStr и нажать Выполнить. Если в данном коде в строке
If -(InStr(Cells(li, 1), sSubStr) > 0) = lMet Then
вместо = lMet указать <> lMet , то удаляться будут строки, не содержащие указанное для поиска значение. Иногда тоже удобно.
Но. Данный код просматривает строки на предмет частичного совпадения указанного значения. Например, если Вы укажете текст для поиска "отчет", то будут удалены все строки, в которых встречается это слово("квартальный отчет", "отчет за месяц" и т.д.). Это не всегда нужно. Поэтому ниже приведен код, который будет удалять только строки, указанные ячейки которых равны конкретно указанному значению:Здесь так же, как и в случае с предыдущим кодом можно заменить оператор сравнения( Cells(li, lCol) = sSubStr ) с равно на неравенство( Cells(li, lCol) <> sSubStr ) и тогда удаляться будут строки, значения ячеек которых не равно указанному.
УДАЛЕНИЕ СТРОК НА ОСНОВАНИИ СПИСКА ЗНАЧЕНИЙ(МНОЖЕСТВЕННЫЕ КРИТЕРИИ)
Иногда бывают ситуации, когда необходимо удалить строки не по одному значению, а по нескольким. Например, если строка содержит или Итог или Отчет. Ниже приведен код, при помощи которого можно удалить строки, указав в качестве критерия диапазон значений.
Значения, которые необходимо найти и удалить перечисляются на листе с именем "Лист2". Т.е. указав на "Лист2" в столбце А(начиная с первой строки) несколько значений - они все будут удалены. Если лист называется иначе(скажем "Соответствия") в коде необходимо будет "Лист2" заменить на "Соответствия". Удаление строк происходит на активном в момент запуска кода листе. Это значит, что перед запуском кода надо перейти на тот лист, строки в котором необходимо удалить.Sub Del_Array_SubStr() Dim sSubStr As String 'искомое слово или фраза Dim lCol As Long 'номер столбца с просматриваемыми значениями Dim lLastRow As Long, li As Long Dim avArr, lr As Long Dim arr lCol = Val(InputBox("Укажите номер столбца, в котором искать указанное значение", "www.excel-vba.ru", 1)) If lCol = 0 Then Exit Sub Application.ScreenUpdating = 0 lLastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count 'заносим в массив значения листа, в котором необходимо удалить строки arr = Cells(1, lCol).Resize(lLastRow).Value 'Получаем с Лист2 значения, которые надо удалить в активном листе With Sheets("Лист2") 'Имя листа с диапазоном значений на удаление avArr = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp)) End With 'удаляем Dim rr As Range For lr = 1 To UBound(avArr, 1) sSubStr = avArr(lr, 1) For li = 1 To lLastRow 'цикл с первой строки до конца If CStr(arr(li, 1)) = sSubStr Then If rr Is Nothing Then Set rr = Cells(li, 1) Else Set rr = Union(rr, Cells(li, 1)) End If End If DoEvents Next li DoEvents Next lr If Not rr Is Nothing Then rr.EntireRow.Delete Application.ScreenUpdating = 1 End Sub
Чтобы код выше удалял строки не по точному совпадению слов, а по частичному(например, в ячейке записано "Привет, как дела?", а в списке есть слово "привет" - надо удалить, т.к. есть слово "привет"), то надо строку:
If CStr(arr(li, 1)) = sSubStr Then
заменить на такую:
If InStr(1, arr(li, 1), sSubStr, 1) > 0 Then
УДАЛЕНИЕ ИЗ ЛИСТА СТРОК, КОТОРЫХ НЕТ В СПИСКЕ ЗНАЧЕНИЙ(МНОЖЕСТВЕННЫЕ КРИТЕРИИ)
Т.к. в последнее время стало поступать все больше и больше вопросов как не удалять значения по списку, а наоборот - оставить в таблице только те значения, которые перечислены в списке - решил дополнить статью и таким кодом.
Значения, которые необходимо оставить перечисляются на листе с именем "Лист2". Т.е. указав на "Лист2" в столбце А(начиная с первой строки) несколько значений - после работы кода на листе будут оставлены только те строки, в которых присутствует хоть одно из перечисленных в списке значений. Если лист называется иначе(скажем "Соответствия") в коде необходимо будет "Лист2" заменить на "Соответствия". Удаление строк происходит на активном в момент запуска кода листе. Это значит, что перед запуском кода надо перейти на тот лист, строки в котором необходимо удалить.
В отличие от приведенных выше кодов, данный код ориентирован на то, что значения в списке указаны не полностью. Т.е. если необходимо оставить только те ячейки, в которых встречается слово "активы", то в списке надо указать только это слово. В этом случае если в ячейке будет записана фраза "Нематериальные активы" или "Активы сторонние" - эти ячейки не будут удалены, т.к. в них встречается слово "активы". Регистр букв при этом неважен.'процедура оставляет в листе только те значения, которые перечислены в списке Sub LeaveOnlyFoundInArray() Dim sSubStr As String 'искомое слово или фраза Dim lCol As Long 'номер столбца с просматриваемыми значениями Dim lLastRow As Long, li As Long Dim avArr, lr As Long Dim arr Dim IsFind As Boolean lCol = Val(InputBox("Укажите номер столбца, в котором искать указанное значение", "www.excel-vba.ru", 1)) If lCol = 0 Then Exit Sub Application.ScreenUpdating = 0 lLastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count 'заносим в массив значения листа, в котором необходимо удалить строки arr = Cells(1, lCol).Resize(lLastRow).Value 'Получаем с Лист2 значения, которые надо удалить в активном листе With Sheets("Лист2") 'Имя листа с диапазоном значений на удаление avArr = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp)) End With 'удаляем Dim rr As Range For li = 1 To lLastRow 'цикл с первой строки таблицы до конца IsFind = False For lr = 1 To UBound(avArr, 1) 'цикл по списку значений на удаление sSubStr = avArr(lr, 1) If InStr(1, arr(li, 1), sSubStr, 1) > 0 Then IsFind = True End If DoEvents Next lr 'если значение таблицы не найдено в списке - удаляем строку If Not IsFind Then If rr Is Nothing Then Set rr = Cells(li, 1) Else Set rr = Union(rr, Cells(li, 1)) End If End If DoEvents Next li If Not rr Is Nothing Then rr.EntireRow.Delete Application.ScreenUpdating = 1 End Sub
Чтобы код выше сравнивал значения таблицы со значениями списка по точному совпадению слов, а не по частичному, то надо строку:
макрос удалит на листе все строки, в которых содержится искомый текст:
Чтобы вместо удаления просто скрыть такие строки, замените строку
Расширенная версия этого макроса - с использованием UserForm для ввода искомого значения
Ещё один вариант кода, позволяющего выполнять поиск (с последующим удалением или скрытием строк) сразу по нескольким условиям:
Комментарии
Подскажите, пожалуйста, какие изменения нужно внести в ваш макрос для скрытия просто пустых строк в диапазоне от 1 до 150 строки.
А как адаптировать Ваш макрос для ячейки с условием, например удалять строку если в ячейке значение больше, например, нуля?
уже все работает)
Не знаю, что вы там делаете, но строка "If ws.Index = 1 Then" явно не из моего макроса
(в моем макросе нет переменной ws)
Вот если вы показали бы полный код своего макроса - тогда можно было бы сказать, в чем именно ошибка.У меня не удаляет ничего( Что я делаю не правильно?
удалять только на первом листе, который называется "накладная"
If ws.Index = 1 Then
Правильно?Спасибо. Все работает как нужно - с Вашей помощью.
А вы попробуйте сначала удалить нули из ячеек
А потом удаляйте строки с пустыми ячейками:
Просмотрел много форумов по удалению строк через макрос. Нашел один который частично подходит мне, он удаляет строки если в 5 столбце пусто во всех листах кроме первого. Но не знаю как добавить, чтоб удалял еще и строки если в 4 столбце значение 0. Код быстрый (без цикла) а как дополнить - ума не приложу.
Sub DeleteEmptyRowsToAll()
Application.ScreenUpdating = False ' отключаем обновление экрана
On Error Resume Next
Dim ws As Worksheet 'декларирование переменой
For Each ws In Worksheets
If ws.Index > 1 Then ' кроме первого листа
ws.Columns(5).SpecialCells(xlCellTypeBlanks).EntireRow.Delete ' удаляем сразу все строки, в которых в 5-м столбце - пусто
End If
Next
End SubОгромное спасибо за помощь. :)
Про чёрные ячейки в столбце.
Файл обновляется несколько раз в день - делать выборку вручную не вариант, нужен макрос.
А офис 2007 покупать шеф не хочет - 18 компов. Хватит вам и 2003, тем более официальный.
Про форум - пардон, не так назвал. Извините, если обидел.Используйте фильтр по цвету (он доступен в Excel 2007 и выше): скройте все чёрные ячейки в столбце, после чего удалите видимые строки, и отключите фильтрацию.
Всё быстро и просто - и никаких макросов.PS: Про какой форум речь? У меня на сайте форума нет, и не предвидится в ближайшем будущем.
Очень хороший форум! Часто им пользуюсь, а вот теперь забуксовал.
задача: имеется таблица, в которой, например, третий из столбец заполнен по принципу - или черная или белая ячейка. Нужно удалить строки с белыми ячейками в этом столбце. Заранее спасибо!Вовсе необязательно вкладывать 3 условия в макрос.
Я бы на вашем месте в дополнительные столбец поместил формулу типа такой:
=ЕСЛИ(ИЛИ(Q1<5;НЕ(ЕОШ(НАЙТИ("текст";I1;1)));И(СЧЁТЕСЛИ(L:L;L1)=1;Q1>2));"";"не удаляем")И потом бы макросом (или через автофильтр) удалил пустые строки по доп.столбцу
Добрый день, подскажите как написать макрос для скрытия и открытия (Или удаления) строк таблицы если в диапазоне ячеек [Пример:(d5:g100)] строка содержит в ячейках нули (или формулу, но отображает по формуле ноль)
Это как раз то что я так долго искал! Большое спасибо за информацию!
Помогите пожалуйста решить чуть более сложную задачу.
Есть несколько условий для удаления,
1) если значение ячейки (числовое) в столбце "Q" меньше "число" - удаляем строку
2) если значение ячейки (текстовое) в столбце "I" содержит "текст" - удаляем строку
И самое, наверное сложное:
3) если значение ячейки (текстовое) в столбце "L" встречается на листе только 1 раз и при этом значение ячейки (числовое) в столбце "Q" больше "число" - удаляем строкуИ как все эти условия правильно в циклы вложить
PS В файле больше 30000 строкЗаранее спасибо! С уважением!
Большое спасибо за помощь! Работает!
Чтобы обработка ячеек начиналась с 15-строки, надо изменить анализируемый диапазон:
Т.е. поиск будет производится на пересечении используемого диапазона листа, и диапазона строк с 15-й по последнюю строку листа
(таким образом мы отсекаем первые 14 строк)Кир Булычев, причину такого поведения вашего макроса я не знаю.
Надо смотреть файл - возможно, проблема в нём.
И, кстати, для 10000 строк надо использовать другие, более правильные и быстрые, алгоритмы
(хотя бы потому, что Union работает медленно, и не может вмещать в себя более 1000 ячеек. Т.е. если у вас с нулями и единицами будет 1500 строк, - то удалится только около 1000 строк, остальные макрос пропустит)PS: За помощью лучше обращайтесь на форумы по Excel
(я помогаю не бесплатно)Вот пользовался макросом который Вы посоветовали (я заменил только локацию данных):
For Each cell In Range("O:O").SpecialCells(xlCellTypeConstants)
If Val(cell) = 0 Or Val(cell) = 1 Then
If delra Is Nothing Then Set delra = cell Else Set delra = Union(delra, cell)
End If
Next cell
If Not delra Is Nothing Then Intersect(delra.EntireRow, Range("M:O")).Delete
Intersect(Range("O:O").SpecialCells(xlCellTypeConstants, xlErrors).EntireRow, Range("M:O")).Delete- но вот недавно, увеличилось число обрабатываемых данных (со 180 до 380 строк, а будет и до 10 000) и если раньше макрос удалял данные в строках с "М" по "О" если в "О" содержатся "1", "0" или "N/A" и поднимал их вверх (т.е. удалял строки) - то теперь он также удаляет данные НО стирает не строки а 2 соседних столбца справа. При возврате к меньшему кол-ву обрабатываемых ячеек/данных - всё снова работает нормально.
Решил опять к Вам обратится за помощью т.к. составные этого макроса к сожалению находятся за граню моих познаний в VBA.Буду очень благодарен если посоветуете что-нибудь.
Скажите, пжта, если надо начать рассматривать массив начиная допустим с 15-й строки. Какие изменения претерпит макрос?
Здравствуйте !
У меня вопрос по макросу "позволяющему выполнять поиск (с последующим удалением или скрытием строк) сразу по нескольким условиям:"Мне нужно чтоб удалялись строки со значением НОЛЬ.
Ставлю "0" и удаляются все значения в значении которого есть НОЛЬ.Как сделать что бы такого не происходила! Сохранив при этом удаление по нескольким условиям.
А можно как-то сделать так, чтобы этот макрос всегда был включен при заходе в файл Exela и работал динамически. К примеру, если поменяются данные в ячейках C2:CI2, то сразу скроются или отобразяться ячейки согласно макросу.
Вы правы. Спасибо большое, он работает хорошо! У меня неполучилось потому что я буквы ставил в ячейки, если подставить цифры то все будет работать правильно :)
Неужели? Плохо проверяли.
Этому макросу нет разницы, что в ячейках - формулы или значения.Sub СкрываемПустыеСтолбцы()
Dim cell As Range: Application.ScreenUpdating = False
For Each cell In [c2:ci2].Cells
' скрываем столбцы, если в ячейке ноль или пусто
cell.EntireColumn.Hidden = Val(cell) = 0
Next cell
End SubСпасибо большое за данный макрос, но он не совсем работает так как мне нужно, я проверил его и окозалось что он опознает ячейки только с введенными данными в ручную, а формулы он не распознает. Допиши пожалуйста макрос, который сможет определять данный полученные формулами в этих ячейках.
Моя задача решена. Спасибо огромное))) В целом - действовала по предложенному Вами плану))) Спасибо!)))))
В макросе в выражении ra.Find(word, , xlValues , xlPart) есть опция xlValues - она отвечает за поиск В ЗНАЧЕНИЯХ.
Есть и другие варианты этой опции:
xlFormulas - поиск в формулах
xlComments - поиск в комментариях к ячейкеСпасибо большое за данный макрос, но он не совсем работает так как мне нужно, я проверил его и окозалось что он опознает ячейки только с введенными данными в ручную, а формулы он не распознает. Допиши пожалуйста макрос, который сможет определять данный полученные формулами в этих ячейках.
Как бы для меня очень затруднительным получается п.3. ((((
Т.е. создать бегунок по строкам (нахожу совпадение - копирую) нереально?(((((
Здравствуйте, Ольга.
Тут многое зависит от того, насколько "огромный" ваш файл (тысячи, десятки или сотни тысяч, миллион строк?)
При разных объёмах данных - разные алгоритмы.Представленный мной код корректно работает для небольших таблиц (где число скрываемых\удаляемых строк не превышает тысячи)
Это ограничение легко обойти, но для увеличения производительности необходимо полностью изменить алгоритм макроса
(что моментально работает на тысячах строк, может нещадно тормозить на миллионе строк)Самый простой способ, который приходит мне на ум - с использованием формулы в доп.столбце:
1) на отдельном листе делаем список из 50 позиций
2) в дополнительном (пустом) столбце с огромной таблицей пишем формулу, которая проверяет наличие одного из 50 слов в строке
3) применяем автофильтр в этому доп. столбцу, оставляя видимыми лишь те строки, для которых формула вернула результат "совпадение найдено"
4) выделяем и копируем видимые строки на другой листПодскажите, пожалуйста, как можно решить такую задачу:
В документе Excel нужно организовать поиск: сразу искать по 50 позиций (огромный файл нужен быстрый поиск по огромному кол-ву материалов)и выводить на новый лист уже найденные позиции и все, что соответствует этим позициям в строке (например, найти среди строительных материалов песок, гравий и т.д. и вывести вместе с количеством на новый лист). Даже не знаю, как лучше эти 50 позиций для поиска делать: лучше бы через UserForm, но пока не получается((((Буду признательна за любую помощь.
Спасибо)Вам поможет такой макрос:
(не самый быстрый - но экономить миллисекунды не вижу смысла)Читайте также: