Excel vba выделить несколько строк
Добрый день.
Подскажите пожалуйста, задача следующая:
Необходимо пройтись по всем рядам таблицы.
Если в колонке А стоит "-" и в колонке В стоит "-", то данную строку необходимо удалить.
Можно ли реализовать данную задачу через макросы?
Спасибо.
Конечно можно. Ставишь на запись макрос, выполняешь какие нужно действия(удаление строки), останавливаешь запись. Открываешь Макросы->Изменить и смотришь какой код получился.
Будет примерно так:
Rows( "1:1" ). Select //выделяем первую строку
Selection.Delete Shift:=xlUp //удаляем со сдвигом вверх
А как правильно организовать перебор всех элементов, и, собственно, если не трудно то условие скажите как задать?
Sub Macros()
Dim rw As Long
For rw = 1 To 100
If Cells(rw, "A" ).Value = "-" And Cells(rw, "B" ).Value = "-" Then
Rows(rw & ":" & rw).Delete Shift:=xlUp
End If
Next rw
End Sub
Работает, но почему-то за один проход не справляется. Причем строк меньше 100. 10 строк удаляет за 4 прохода. Что может останавливать скрипт?
Вообще-то, при удалении строки необходимо уменьшать счётчик, ибо в противном случае могут остаться строки, содержащие "-" (разумеется, если "-" содержатся в смежных ячейках) или начать удаление снизу вверх, т.е.
Private Sub Test()
Application.ScreenUpdating = False
Dim rw&
For rw = 100 To 1 Step -1
If Cells(rw, 1) = "-" And Cells(rw, 2) = "-" Then Rows(rw).Delete
Next
Application.ScreenUpdating = True
Чуть не забыл :) если в дальнейшем потребуется проверять наличие "-" в большем количестве столбцов, чем сейчас, то вместо увеличения And или дополнительного цикла по столбцам, можно программно воспользоваться стандартной функцией рабочего листа =СЧЁТЕСЛИ() (см. листинг)
Private Sub Test2()
Dim rw&, ws As Worksheet
Set ws = ActiveSheet
With Application
.ScreenUpdating = False
For rw = 100 To 1 Step -1
If .CountIf(ws.Cells(rw, "A").Resize(, 2), "-") = 2 Then ws.Rows(rw).Delete
Next
.ScreenUpdating = True
End With
В Microsoft Excel можно выбирать ячейки в диапазоне вручную или с помощью простого кода Microsoft Visual Basic для приложений. В этой статье приводится ряд примеров, которые можно использовать для выбора диапазона ячеек.
Дополнительная информация
Примеры выбора ячеек вручную
Чтобы вручную выбрать все данные в столбце, выберите первую ячейку и нажмите клавиши CTRL+SHIFT+СТРЕЛКА ВНИЗ.
Точно так же, чтобы вручную выбрать строку и все вложенные в нее столбцы, нажмите клавиши CTRL+SHIFT+СТРЕЛКА ВНИЗ+СТРЕЛКА ВПРАВО. Однако все данные должны быть неумными (т. е. пустые строки или столбцы не могут быть). Кроме того, вы можете выбрать текущую область данных (несколько строк или столбцов без пустых строк и столбцов).
В меню Правка выберите пункт Перейти.
В диалоговом окне Перейти нажмите кнопку Специальный.
В диалоговом окне Перейти к специальным выберите текущую область и нажмите кнопку
ОК.
Вы также можете выбрать этот диапазон с помощью простого Visual Basic для приложений кода.
Примечание. Если попытаться записать эту процедуру с помощью записи макроса, результаты будут не одинаковыми.
Примеры использования кода Visual Basic для выбора ячеек в диапазоне
Корпорация Майкрософт предоставляет примеры программирования только для иллюстрации без гарантии, выраженной или подразумеваемой. В частности, это подразумевает подразумеваемые гарантии пригодности для купли-связи или пригодности для определенной цели. В этой статье предполагается, что вы знакомы с языком программирования, который демонстрируется, и средствами, используемыми для создания и от debug procedures. Специалисты службы поддержки Майкрософт могут объяснить функциональные возможности конкретной процедуры, но не будут изменять эти примеры, чтобы предоставить дополнительные функции или процедуры по построению с учетом ваших конкретных требований. В примерах Visual Basic ниже покажите, как выбрать разные диапазоны.
Если вы знаете следующую ячейку (в данном примере — ячейка C1) и хотите выбрать ее до последней ячейки (в том же столбце), которая имеет данные, но не знает этот адрес, используйте следующий код:
Примечание. Макрос SelectRangeDown предполагает, что данные не должны быть соединимы. В противном случае, если в выбранном столбце данных есть пустые ячейки, этот макрос может не выбрать все ячейки в этом столбце.
Если данные начинаются с ячейки C1, но не являются неровными в этом столбце, используйте следующий макрос в Microsoft Office Excel 2003 и более ранних версиях Excel:
Так как Microsoft Office Excel 2007 поддерживает 1 048 576 строк, используйте следующий макрос в Excel 2007:
Если вы хотите выбрать активную ячейку вниз и все столбцы справа (предполагается, что данные есть во всех строках и столбцах), используйте следующий код:
Если вы знаете следующую следующую ячейку (в этом примере — ячейка D1) и хотите выбрать ее вниз и справа, используйте следующий код:
Чтобы выбрать все данные в текущем регионе, используйте следующий код:
В примерах, которые содержатся в этой статье, покажите, как выбрать различные диапазоны на активном компьютере текущей книги.
Чтобы получить дополнительные сведения о том, как выбрать диапазоны с помощью Visual Basic для приложений (и для более сложных примеров), щелкните номер следующей статьи, чтобы просмотреть статью в базе знаний Майкрософт:
У вас большой монитор, но таблицы, с которыми вы работаете - еще больше. И, пробегая взглядом по экрану в поисках нужной информации, всегда есть шанс "соскользнуть" взглядом на соседнюю строчку и посмотреть не туда. Я даже знаю людей, который для таких случаев постоянно держат недалеко от себя деревянную линейку, чтобы приложить ее к строке на мониторе. Технологии будущего!
А если при движении активной ячейки по листу будет подсвечиваться текущая строка и столбец? Своего рода координатное выделение примерно такого вида:
Поудобнее, чем линейка, правда?
Есть несколько способов разной сложности, чтобы реализовать такое. Каждый способ - со своими плюсами и минусами. Давайте разберем их детально.
Способ 1. Очевидный. Макрос, выделяющий текущую строку и столбец
Самый очевидный путь для решения нашей проблемы "в лоб" - нам нужен макрос, который будет отслеживать изменение выделения на листе и выделять целую строку и столбец для текущей ячейки. Также желательно иметь возможность при необходимости включать и отключать эту функцию, чтобы такое крестообразное выделение не мешало нам вводить, например, формулы, а работало только тогда, когда мы просматриваем список в поисках нужной информации. Это приводит нас к трем макросам (выделения, включения и выключения), которые нужно будет добавить в модуль листа.
Откройте лист со таблицей, в которой хотите получить такое координатное выделение. Щелкните правой кнопкой мыши по ярлычку листа и выберите в контекстном меню команду Исходный текст (Source Code). Должно открыться окно редактора Visual Basic. Скопируйте в него этот текст этих трех макросов:
Измените адрес рабочего диапазона на свой - именно в пределах этого диапазона и будет работать наше выделение. Затем закройте редактор Visual Basic и вернитесь в Excel.
Нажмите сочетание клавиш ALT+F8, чтобы открыть окно со списком доступных макросов. Макрос Selection_On, как нетрудно догадаться, включает координатное выделение на текущем листе, а макрос Selection_Off - выключает его. В этом же окне, нажав кнопку Параметры (Options) можно назначить этим макросам сочетания клавиш для удобного запуска.
Плюсы этого способа:
- относительная простота реализации
- выделение - операция безобидная и никак не изменяет содержимое или форматирование ячеек листа, все остается как есть
Минусы этого способа:
- такое выделение некорректно работает в том случае, если на листе есть объединенные ячейки - выделяются сразу все строки и столбцы, входящие в объединение
- если случайно нажать клавишу Delete, то очистится не только активная ячейка, а вся выделенная область, т.е. удалятся данные из всей строки и столбца
Способ 2. Оригинальный. Функция ЯЧЕЙКА + Условное форматирование
Этот способ хотя и имеет пару недостатков, мне представляется весьма изящным. Реализовать что-либо, используя только встроенные средства Excel, минимально влезая в программирование на VBA - высший пилотаж ;)
Способ основан на использовании функции ЯЧЕЙКА (CELL), которая может выдавать массу различной информации по заданной ячейке - высоту, ширину, номер строки-столбца, числовой формат и т.д.. Эта функция имеет два аргумента:
- кодовое слово для параметра, например "столбец" или "строка"
- адрес ячейки, для которой мы хотим определить значение этого параметра
Хитрость в том, что второй аргумент не является обязательным. Если он не указан, то берется текущая активная ячейка.
Вторая составляющая этого способа - условное форматирование. Эта крайне полезная функция Excel позволяет автоматически форматировать ячейки, если они удовлетворяют заданным условиям. Если соединить эти две идеи в одно целое, то получим следующий алгоритм реализации нашего координатного выделения через условное форматирование:
- Выделяем нашу таблицу, т.е. те ячейки, в которых в будущем должно отображаться координатное выделение.
- В Excel 2003 и более старших версиях открываем меню Формат - Условное форматирование - Формула (Format - Conditional Formatting - Formula) . В Excel 2007 и новее - жмем на вкладке Главная (Home) кнопку Условное форматирование - Создать правило (Conditional Formatting - Create Rule) и выбираем тип правила Использовать формулу для определения форматируемых ячеек (Use formula)
- Вводим формулу для нашего координатного выделения:
Все почти готово, но остался один нюанс. Дело в том, что Excel не считает изменение выделения изменением данных на листе. И, как следствие, не запускает пересчет формул и перекраску условного форматирования только при изменении положения активной ячейки. Поэтому добавим в модуль листа простой макрос, который будет это делать. Щелкните правой кнопкой мыши по ярлычку листа и выберите в контекстном меню команду Исходный текст (Source Code). Должно открыться окно редактора Visual Basic. Скопируйте в него этот текст этого простого макроса:
Теперь при изменении выделения будет запускаться процесс пересчета формулы с функцией ЯЧЕЙКА в условном форматировании и заливаться текущая строка и столбец.
Плюсы этого способа:
- Условное форматирование не нарушает пользовательское форматирование таблицы
- Этот вариант выделения корректно работает с объединенными ячейками.
- Нет риска удалить целую строку и столбец с данными при случайном нажатии Delete.
- Макросы используются минимально
Минусы этого способа:
- Формулу для условного форматирования надо вводить вручную.
- Нет быстрого способа включить-выключить такое форматирование - оно включено всегда, пока не будет удалено правило.
Способ 3. Оптимальный. Условное форматирование + макросы
Золотая середина. Используем механизм отслеживания выделения на листе при помощи макросов из способа-1 и добавим к нему безопасное выделение цветом с помощью условного форматирования из способа-2.
Откройте лист со таблицей, в которой хотите получить такое координатное выделение. Щелкните правой кнопкой мыши по ярлычку листа и выберите в контекстном меню команду Исходный текст (Source Code). Должно открыться окно редактора Visual Basic. Скопируйте в него этот текст этих трех макросов:
Не забудьте изменить адрес рабочего диапазона на адрес своей таблицы. Закройте редактор Visual Basic и вернитесь в Excel. Чтобы использовать добавленные макросы, нажмите сочетание клавиш ALT+F8 и действуйте аналогично способу 1.
Способ 4. Красивый. Надстройка FollowCellPointer
Excel MVP Jan Karel Pieterse родом из Нидерландов раздает у себя на сайте бесплатную надстройку FollowCellPointer(36Кб), которая решает ту же задачу, отрисовывая с помощью макросов графические линии-стрелки для подсветки текущей строки и столбца:
Красивое решение. Не без глюков местами, но попробовать точно стоит. Качаем архив, распаковываем на диск и устанавливаем надстройку:
Range.Cut – это метод, который вырезает объект Range (диапазон ячеек) в буфер обмена или перемещает его в указанное место на рабочем листе.
Синтаксис
Параметры
Параметры | Описание |
---|---|
Destination | Необязательный параметр. Диапазон ячеек рабочего листа, в который будет вставлен (перемещен) вырезанный объект Range (достаточно указать верхнюю левую ячейку диапазона). Если этот параметр опущен, объект вырезается в буфер обмена. |
Для вставки на рабочий лист диапазона ячеек, вырезанного в буфер обмена методом Range.Cut, следует использовать метод Worksheet.Paste.
Метод Range.Copy
Range.Copy – это метод, который копирует объект Range (диапазон ячеек) в буфер обмена или в указанное место на рабочем листе.
Синтаксис
Параметры
Параметры | Описание |
---|---|
Destination | Необязательный параметр. Диапазон ячеек рабочего листа, в который будет вставлен скопированный объект Range (достаточно указать верхнюю левую ячейку диапазона). Если этот параметр опущен, объект копируется в буфер обмена. |
Метод Worksheet.Paste
Синтаксис
Метод Worksheet.Paste работает как с диапазонами ячеек, вырезанными в буфер обмена методом Range.Cut, так и скопированными в буфер обмена методом Range.Copy.
Параметры
Параметры | Описание |
---|---|
Destination | Необязательный параметр. Диапазон (ячейка), указывающий место вставки содержимого буфера обмена. Если этот параметр не указан, используется текущий выделенный объект. |
Link | Необязательный параметр. Булево значение, которое указывает, устанавливать ли ссылку на источник вставленных данных: True – устанавливать, False – не устанавливать (значение по умолчанию). |
В выражении с методом Worksheet.Paste можно указать только один из параметров: или Destination, или Link.
Для вставки из буфера обмена отдельных компонентов скопированных ячеек (значения, форматы, примечания и т.д.), а также для проведения транспонирования и вычислений, используйте метод Range.PasteSpecial (специальная вставка).
Примеры
Вырезание и вставка диапазона одной строкой (перемещение):
Вырезание ячеек в буфер обмена и вставка методом ActiveSheet.Paste:
Копирование и вставка диапазона одной строкой:
Копирование ячеек в буфер обмена и вставка методом ActiveSheet.Paste:
Копирование одной ячейки и вставка ее данных во все ячейки заданного диапазона:
18 комментариев для “VBA Excel. Вырезание, копирование и вставка ячеек (диапазонов)”
Странно, что не рассмотрено копирование ячеек, которые Cells.
Например, следующая строка копирует ячейку A1 в B2
а эта делает тоже самое, но демонстрирует,
как можно добавить размер и смещение:
Cells ( 1 , "A" ) . Resize ( 1 , 1 ) . Offset ( 0 , 0 ) . Copy Cells ( 2 , "B" ) . Resize ( 1 , 1 ) . Offset ( 0 , 0 )
Здравствуйте!
Скажите, как можно копировать на Лист 1, а вставить на Лист 2 диапазон ячеек целиком, либо отсортированный по 1 признаку диапазон(например по какому-то определенному значению, скажем, числу «500» в одном столбце Листа 1)?
Привет, Максим!
Вот пример сортировки таблицы по значению 500 в первом столбце на "Лист6" и копирования диапазона с "Лист6" на "Лист4" :
Спасибо большое, только я, наверно, неправильно описал ситуацию.
Сперва из эталонной таблицы Лист 1 копируем весь список на существующий Лист4.
Затем, пропускаем шапку, и, начиная со строки7, уже на Лист4 ищем строку, у которой в столбце 5 стоит число 500.
Вырезаем эту строку и вставляем на позицию строки 7. т.е первая после шапки.
Ищем следующую строку — вырезаем, потом вставляем на позицию строки 7+1=8 и т.д.
В итоге оставим только те строки, у которых в столбце 5 стоит число «500», остальные удаляются строки.
(пробовал менять местами строки в предыдущем коде, ошибок нет, копирует, переходит на лист 4, но строки на нем не удаляет, циклы пошагово проходят, но ничего не изменяется.)
Необходимо выделить несколько ячеек указывая столбцы числом, а не буквой, т.е.
ActiveCell.Offset(3, 1).Range("A1:C4").Select
не прокатит.
Cells тоже нельзя использовать. т.е.
ActiveCell.Offset(3, 1).Range(Cells(1,1), Cells(4,4)).Select
тоже нельзя использовать.
Если кто-нибудь знает иной способ - подскажите.
Originally posted by Oracloid
Необходимо выделить несколько ячеек указывая столбцы числом, а не буквой, т.е.
ActiveCell.Offset(3, 1).Range("A1:C4").Select
не прокатит.
Cells тоже нельзя использовать. т.е.
ActiveCell.Offset(3, 1).Range(Cells(1,1), Cells(4,4)).Select
тоже нельзя использовать.
Если кто-нибудь знает иной способ - подскажите.
Что-то ты замудрил.
ActiveSheet.Range(Cells(1, 2), Cells(3, 7)).Select
Или пиши подробнее, что тебе можно, что нельзя и почему нельзя. :)
Дело вот какое:
Обращатся к ячейкам через букву сложнее, чем просто указывать номер столбца и номер ячейки. Например нужно написать чтобы по циклу все пробежать и ты не ломаешь голову.
Использую Forms (Oracle-овый продукт) и OLE для того чтобы управлять Екселом.
Исходный текст выглядит так:
ArgList:=OLE2.CREATE_ARGLIST; --передаваемый аргумент
OLE2.ADD_ARG(ArgList, 'A1');
OLE2.ADD_ARG(ArgList, 'B5');
cell:=OLE2.get_OBJ_property(worksheet, 'range', ArgList);
OLE2.DESTROY_ARGLIST(ArgList);
OLE2.Invoke(cell,'select');
Это работает.
Но хотелось бы передавать координаты ячеек указывая столбец цифрой, а не буквой.
Например:
OLE2.ADD_ARG(ArgList, '1:1');--верхняя левая
OLE2.ADD_ARG(ArgList, '2:5');--нижнаяя правая
Я не могу передать в качестве аргумента 'Cell(1,1)' - нужны конкретные значения
Originally posted by Oracloid
Дело вот какое:
Обращатся к ячейкам через букву сложнее, чем просто указывать номер столбца и номер ячейки. Например нужно написать чтобы по циклу все пробежать и ты не ломаешь голову.
Использую Forms (Oracle-овый продукт) и OLE для того чтобы управлять Екселом.
Исходный текст выглядит так:
ArgList:=OLE2.CREATE_ARGLIST; --передаваемый аргумент
OLE2.ADD_ARG(ArgList, 'A1');
OLE2.ADD_ARG(ArgList, 'B5');
cell:=OLE2.get_OBJ_property(worksheet, 'range', ArgList);
OLE2.DESTROY_ARGLIST(ArgList);
OLE2.Invoke(cell,'select');
Это работает.
Но хотелось бы передавать координаты ячеек указывая столбец цифрой, а не буквой.
Например:
OLE2.ADD_ARG(ArgList, '1:1');--верхняя левая
OLE2.ADD_ARG(ArgList, '2:5');--нижнаяя правая
Я не могу передать в качестве аргумента 'Cell(1,1)' - нужны конкретные значения
Читайте также: