wwwoldi.ru

About Me

Dashy

Hello word I am dashy Guy

Разное

Копирование строк по условию vba excel: Метод Range.Copy (Excel) | Microsoft Docs

alexxlab
No Comments

Содержание

Метод Range.Copy (Excel) | Microsoft Docs



  • Чтение занимает 2 мин

В этой статье

Копирует диапазон в указанный диапазон или в буфер обмена.Copies the range to the specified range or to the Clipboard.

Примечание

Хотите создавать решения, которые расширяют возможности Office на разнообразных платформах?Interested in developing solutions that extend the Office experience across multiple platforms? Ознакомьтесь с новой моделью надстроек Office.Check out the new Office Add-ins model. У надстроек Office мало места по сравнению с надстройками и решениями VSTO, которые можно создавать с помощью практически любой технологии веб-программирования, например HTML5, JavaScript, CSS3 и XML. Office Add-ins have a small footprint compared to VSTO Add-ins and solutions, and you can build them by using almost any web programming technology, such as HTML5, JavaScript, CSS3, and XML.

СинтаксисSyntax

выражение.Copy (Destination)expression.Copy (Destination)

выражение: переменная, представляющая объект Range.expression A variable that represents a Range object.

ПараметрыParameters

ИмяNameОбязательный или необязательныйRequired/OptionalТип данныхData typeОписаниеDescription
DestinationDestinationНеобязательныйOptionalVariantVariantУказывает новый диапазон, в который будет скопирован заданный диапазон.Specifies the new range to which the specified range will be copied. Если этот аргумент опущен, Microsoft Excel копирует диапазон в буфер обмена.If this argument is omitted, Microsoft Excel copies the range to the Clipboard.

Возвращаемое значениеReturn value

VariantVariant

ПримерExample

В следующем примере кода копируются формулы ячеек A1:D4 с листа Sheet1 в ячейки с E5:H8 на листе Sheet2.The following code example copies the formulas in cells A1:D4 on Sheet1 into cells E5:H8 on Sheet2.

Worksheets("Sheet1").Range("A1:D4").Copy _ 
    destination:=Worksheets("Sheet2").Range("E5")

В приведенном ниже примере кода проверяется значение столбца D для каждой строки на листе Sheet1.The following code example inspects the value in column D for each row on Sheet1. Если значение в столбце D равно «A», вся строка копируется на лист SheetA в следующую пустую строку.If the value in column D equals A, the entire row is copied onto SheetA in the next empty row. Если значение равно «B», строка копируется на лист SheetB. If the value equals B, the row is copied onto SheetB.

Public Sub CopyRows() 
    Sheets("Sheet1").Select 
    ' Find the last row of data 
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row 
    ' Loop through each row 
    For x = 2 To FinalRow 
        ' Decide if to copy based on column D 
        ThisValue = Cells(x, 4).Value 
        If ThisValue = "A" Then 
            Cells(x, 1).Resize(1, 33).Copy 
            Sheets("SheetA").Select 
            NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1 
            Cells(NextRow, 1).Select 
            ActiveSheet.Paste 
            Sheets("Sheet1").Select 
        ElseIf ThisValue = "B" Then 
            Cells(x, 1).Resize(1, 33).Copy 
            Sheets("SheetB").Select 
            NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1 
            Cells(NextRow, 1).Select 
            ActiveSheet.Paste 
            Sheets("Sheet1").Select 
        End If 
    Next x 
End Sub

Поддержка и обратная связьSupport and feedback

Есть вопросы или отзывы, касающиеся Office VBA или этой статьи?Have questions or feedback about Office VBA or this documentation? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.



Работа с диапазонами в VBA

Настоящая заметка продолжает знакомство с VBA, в ней описана работа с диапазонами в VBA.[1]

Рис. 1. Пример, демонстрирующий, как выделять диапазоны различной формы в VBA$ чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Скачать заметку в формате Word или pdf, примеры в архиве (политика безопасности провайдера не позволяет загружать файлы Excel с поддержкой макросов)

Копирование диапазона

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

Sub Макрос()
    Range("A1"). Select
    Selection.Copy
    Range("B1").Select
    ActiveSheet.Paste
End Sub

Обратите внимание, что данная программа выделяет ячейки. Однако в VBA для работы с объектом не обязательно его выделять. Данную процедуру можно заменить значительно более простой — применить метод Сору, который использует аргумент, представляющий адрес места вставки копируемого диапазона.

Sub CopyRange()
    Range("А1").Copy Range("В1")
End Sub

Предполагается, что рабочий лист является активным и операция выполняется на активном рабочем листе. Чтобы скопировать диапазон на другой рабочий лист или в другую книгу, необходимо задать ссылку:

Sub CopyRange2()
    Workbooks("File1.xlsx").Sheets("Лист1").Range("A1").Copy _
    Workbooks("File2.xlsx").Sheets("Лист2"). Range("A1")
End Sub

Еще одним подходом к решению этой задачи является использование для представления диапазонов объектных переменных:

Sub CopyRange3()
    Dim Rngl As Range, Rng2 As Range
    Set Rngl = Workbooks("File1.xlsx").Sheets("Лист1").Range("A1")
    Set Rng2 = Workbooks("File2.xlsx").Sheets("Лист2").Range("A1")
Rngl.Copy Rng2 End Sub

Можно копировать большой диапазон. Адрес места вставки определяется единственной ячейкой (представляющей верхний левый угол вставляемого диапазона):

Sub CopyRange4 ()
    Range("А1:С800").Copy Range("D1")
End Sub

Для перемещения диапазона ячеек вместо метода Сору используется метод Cut.

Если размер копируемого диапазона не известен используется свойство CurrentRegion, возвращающее объект Range, который соответствует прямоугольнику ячеек вокруг заданной ячейки:

Sub CopyCurrentRegion2()
    Range("A1"). CurrentRegion.Copy Sheets("Лист2").Range("A1")
End Sub

Метод End имеет один аргумент, определяющий направление, в котором увеличивается выделение ячеек. Следующий оператор выделяет диапазон от активной ячейки до последней непустой ячейки внизу:

Range (ActiveCell, ActiveCell.End(xlDown)).Select

Три остальные константы имитируют комбинации клавиш при выделении в других направлениях: xlUp (вверх), xlToLeft (влево) и xlToRight (вправо).

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

Запрос значения ячейки

Следующая процедура запрашивает значение у пользователя и вставляет его в ячейку А1:

Sub GetValuel()
    Range("A1").Value = InputBox("Введите значение")
End Sub

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

Sub GetValue2()
    Dim UserEntry As Variant
    UserEntry = InputBox("Введите значение")
    If UserEntry <> "" Then Range("A1").Value = UserEntry
End Sub

Во многих случаях следует проверить правильность данных, введенных пользователем. Например, необходимо обеспечить введение только чисел в диапазоне от 1 до 12 (рис. 2). Это можно сделать при помощи процедуры GetValue3(), код которой приведен в Модуле1 приложенного Excel-файла. Некорректные данные игнорируются, и окно запроса значения отображается снова. Этот цикл будет повторяться, пока пользователь не введет правильное значение или не щелкнет на кнопке Отмена.

Рис. 2. Проверка данных, введенных пользователем

Ввод значения в следующую пустую ячейку

Если требуется ввести значение в следующую пустую ячейку столбца или строки, используйте код (рис. 3):

Sub GetData()
    Dim NextRow As Long
    Dim Entry1 As String, Entry2 As String
Do
'   Определение следующей пустой строки
    NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
'   Запрос данных
    Entry1 = InputBox("Введите имя")
    If Entry1 = "" Then Exit Sub
    Entry2 = InputBox("Введите сумму")
    If Entry2 = "" Then Exit Sub
'   Запись данных
    Cells(NextRow, 1) = Entry1
    Cells(NextRow, 2) = Entry2
Loop
End Sub

Рис. 3. Макрос вставляет данные в следующую пустую строку рабочего листа

Это бесконечный цикл. Для выхода из него (щелкните на кнопке Cancel) использовались операторы Exit Sub. Обратите внимание строку, в который определяется значение переменной NextRow. Если вам трудно ее понять, проанализируйте содержимое ячейки: перейдите в последнюю ячейку столбца А и нажмите <End> и <↑>. После этого будет выделена последняя непустая ячейка в столбце А. Свойство Row возвращает номер этой строки; чтобы получить расположенную под ней строку (следующую пустую строку), к этому номеру прибавляется 1.

Приостановка работы макроса для определения диапазона пользователем

В некоторых ситуациях макрос должен взаимодействовать с пользователем. Например, можно создать макрос, который приостанавливается, когда пользователь указывает диапазон ячеек. Для этого воспользуйтесь функцией Excel InputBox. Не путайте метод Excel InputBox с функцией VBA InputBox. Несмотря на идентичность названий, это далеко не одно и то же.

Процедура, представленная ниже, демонстрирует, как приостановить макрос и разрешить пользователю выбрать ячейку. Затем автоматически формула вставляется в каждую ячейку выделенного диапазона.

Sub GetUserRange()
    Dim UserRange As Range
    Prompt = "Выберите диапазон для случайных чисел."
    Title = "Выбор диапазона"
'   Отображение поля ввода
    On Error Resume Next
    Set UserRange = Application.InputBox( _
        Prompt:=Prompt, _
        Title:=Title, _
        Default:=ActiveCell.Address, _
        Type:=8) 'Выделение диапазона
    On Error GoTo 0
'   Отменено ли отображение поля ввода?
    If UserRange Is Nothing Then
        MsgBox "Отменено."
    Else
        UserRange.Formula = "=RAND()"
    End If
End Sub

Окно ввода данных показано на рис. 4. Важный момент в этой процедуре – определение аргумента Туре равным 8 (в этом случае InputBox вернет диапазон; подробнее см. Application.InputBox Method).

Рис. 4. Использование окна ввода данных с целью приостановки выполнения макроса

Оператор On Error Resume Next игнорирует ошибку, если пользователь не выберет диапазон, а щелкает Отмена. В таком случае объектная переменная UserRange не получает значения. В этом случае отобразится окно сообщения с текстом «Отменено». Если же пользователь щелкнет на кнопке OK, то макрос продолжит выполняться. Строка On Error Go То указывает на переход к стандартной обработке ошибки. Проверка корректного выделения диапазона необязательна. Excel позаботится об этом вместо вас.

Обязательно проверьте, включено ли обновление экрана при использовании метода InputBox для выделения диапазона. Если обновление экрана отключено, вы не сможете выделить рабочий лист. Чтобы проконтролировать обновление экрана, в процессе выполнения макроса используйте свойство ScreenUpdating объекта Application.

Подсчет выделенных ячеек

Работая с макросом, который обрабатывает выделенный диапазон ячеек, можно использовать свойство Count, чтобы определить, сколько ячеек содержится в выделенном (или любом другом) диапазоне. Например, оператор MsgBox Selection.Count демонстрирует окно сообщения, которое отображает количество ячеек в текущем выделенном диапазоне. Свойство Count использует тип данных Long, поэтому наибольшее значение, которое может храниться в нем, равно 2 147 483 647. Если выделить лист целиком, то ячеек будет больше, и свойство Count сгенерирует ошибку. Используйте свойство CountLarge, которое не имеет таких ограничений.

Если активный лист содержит диапазон data, то следующий оператор присваивает количество ячеек в диапазоне data переменной с названием CellCount:

CellCount = Range("data").Count

Вы можете также определить, сколько строк или столбцов содержится в диапазоне. Следующее выражение вычисляет количество столбцов в выделенном диапазоне:

Selection.Columns.Count

Следующий оператор пересчитывает количество строк в диапазоне с названием data и присваив

Как работать с объединенными ячейками Excel

Как объединить ячейки в Excel

Сначала расскажем что такое объединенные ячейки в Excel и как их сделать. Тут нет ничего сложно, достаточно выделить две или более ячеек и выбрать команду на ленте Главная -> Выравнивание -> Объединить и поместить в центре.

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

Вы можете объединять ячейки как по горизонтали, так и по вертикали. Также можно объединить области, содержащие боле одной строки и столбца.

Способы объединения ячеек

Если внимательней посмотреть на команду объединения ячеек, то можно увидеть выпадающее меню, которое в свою очередь содержит следующие дополнительные действия:

  • Объединить по строкам — позволяет выбрать диапазон ячеек, который содержит несколько строк. В этом случае Excel будет создавать объединенные ячейки по одной на каждой строке

  • Объединить ячейки — объединяет выделенные ячейки в одну, но при этом не выравнивает текст в ячейке.

  • Отменить объединение ячеек — позволяет разбить объединенную ячейку на несколько.

Какие проблемы возникают при использовании объединенных ячеек

Как уже говорили, пользоваться объединенными ячейками стоит аккуратно, так как они ограничивают функционал Excel и могут в дальнейшем принести неприятности. Если все-таки решили пользоваться объединенными ячейками, всегда помните о следующих моментах:

  • Если диапазон содержит объединенные ячейки, то пользоваться сортировкой и фильтрацией в этом диапазоне будет невозможно.

  • Также невозможно будет преобразовать такой диапазон в таблицу (форматировать как таблицу).

  • Также можно забыть об автоматическом выравнивании ширины или высоты ячейки. Например если имеется объединенная ячейка A1:B1, то выравнять ширину столбца A уже не получится.

  • Если Вы пользуетесь горячими клавишами для навигации, например переходите в начало и конец таблицы путем сочетания клавиш Ctrl + стрелка вверх и вниз, то переход не удастся, и курсор будет «упираться» в объединенные ячейки.

  • Если вы выделяете столбцы (или строки) с помощью горячих клавиш Ctrl (Shift) + Пробел, то при наличии объединенных ячеек, вы не сможете выделить 1 столбец (или строку).

Как найти все объединенные ячейки и разъединить их

Чтобы разъединить все ячейки и удалить объединенные, достаточно выполнить следующие действия:

  1. Выделить все ячейки на листе. Это можно сделать путем нажатия сочетания клавиш Ctrl + A или кликнуть на черный треугольник между заголовками строк и столбцов на листе.

  2. Нажать на команду Главная -> Выравнивание -> Объединить и поместить в центре если она выделена. Если же она не выделена, значит выбранный лист не содержит объединенных ячеек.

Если же необходимо найти все объединенные ячейки в книге, то это можно сделать через панель поиска. Для этого выполните следующие шаги:

  1. Откройте окно Найти и заменить. Сделать это можно сочетанием клавиш Ctrl + F.

  2. Поле Найти оставьте пустым и нажмите на кнопку Параметры.

  3. Должна отобразиться кнопка Формат. Нажмите левой кнопкой мыши по ней.
     

  4. В диалоговом окне Найти формат на вкладке Выравнивание выберите опцию Объединение ячеек. Далее нажмите Ok.

  5. В диалоговом окне Найти и заменить нажмите Найти далее или Найти все в зависимости от вашей дальнейшей задачи.

Альтернативы использования объединенных ячеек

Как правило объединенные ячейки применяют для целей выравнивания текста. Однако мало кто знает, что текст можно выровнять и без объединения ячеек и потери функционала. Для этого можно использовать обычное форматирование ячеек:

  1. Выделите диапазон ячеек, который хотите отцентрировать. При этом сам текст должен содержаться в левой верхней ячейке.

  2. Выберите команду формат ячеек или нажмите сочетание клавиш Ctrl + 1.

  3. В диалоговом окне Формат ячеек перейдите на вкладку Выравнивание.

  4. В выпадающем списке выравнивания по горизонтали выберите значение по центру выделения и нажмите OK.

Еще один способ избежать объединения ячеек — это использовать надписи. В них также можно вставлять текст и выбирать направление текста по вертикали. Способ конечно также накладывает много ограничений, но в каких-то случаях имеет место быть.

Все способы можно скачать нажав кнопку вначале статьи.

Скачать

Как запустить макрос при изменении ячейки?

Полезные макросы Excel. Простые возможности VBA, которые я часто использую

Абзац в Excel. Как начать с новой строки в ячейке

В Excel множество функций, но некоторые возможности не видны на первый взгляд. Неудивительно, ведь неизвестно, что может пригодиться пользователю, поэтому нет смысла выносить все на основную панель. Часто нужно выполнить какое-то действие при определенном условии, решаемое формулой. Но что, если вам необходимо выполнять действие/макрос при изменении ячейки листа Excel. Это тоже можно сделать при помощи макроса VBA. А как запустить макрос при изменении ячейки?

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

Макрос, выполняемый при изменении ячейки
Private Sub Worksheet_Change(ByVal Target As Range)
 If Not Intersect(Target, Range("A1")) Is Nothing Then
   If Target = 0 Then
     MsgBox "Запустите Ваше действие"
   End If
 End If
End Sub

Вставьте вместо MsgBox «Запустите Ваше действие» ваш код. Полезные макросы можно посмотреть в этой статье. Для этого макроса если, ячейка А1 становится нулевой, то появляется сообщение (MsgBox) «Запустите Ваше действие».

Как запустить макрос при изменении ячейки?

Сперва включите вкладку Разработчик. Затем пройдите в меню Visual Basic, первый значок слева на картинке (или нажмите Alt+f11):

Важно! Чтобы запустить код типа Worksheet_Change, необходимо записать код не в модуль макроса, а в саму книгу! Подробнее здесь:

Выберите нужную книгу и нужный лист (в примере Лист1), кликнув два раза по листу и в правом окне вставьте код, указанный выше. Если вам нужно, чтобы ваше действие выполнялось, когда ячейка изменяет значение на заполненную, то нужно изменить текст Target = 0 на Target <> 0.

Лично я рекомендую добавить больше условий, при каких действиях будет выполняться ваше действие, чтобы макрос не запускался при любом изменении. Вообще надо быть аккуратным с этой функцией. Бывает и такое — вы забываете, что есть макрос с условиями, а информация изменяется, можно даже не заметить этого

 

Поделитесь нашей статьей в ваших соцсетях:

Похожие статьи

Полезные макросы Excel. Простые возможности VBA, которые я часто использую

Абзац в Excel. Как начать с новой строки в ячейке

[Решено] Excel VBA автоматически копирует строку на новый лист

У меня есть исходный лист1 с именем (Продажи), в котором есть данные по каждой строке (Один по высоте).
Sheet1 содержит именованный диапазон (ItemName (A2: A100)), который обновляет свои данные из веб-API каждые 10 минут в той же строке. Макет ниже столбцов Sheet1

 A B C D E
1 Наименование Дата продажи Стоимость Продано% Прирост
2 Пункт 1 01.01.2017 .50 1,00 100%
3 Позиция 2 1/2/2017 0,50 1,00 100%
4 Item3 01.01.2017.50 1,00 100%
И Т.П.

У меня есть целевые листы, уже созданные / названные (ItemName1,2,3,4) на основе столбца A (именованный диапазон) «ItemName» из исходного листа.

Я пытаюсь обновить целевой лист данными исходного листа для каждого элемента после обновления исходного листа каждые 10 минут. когда исходный лист обновляется из веб-API, данные обновляются в той же строке ITEM. это означает, что на исходном листе не создаются новые строки.

Я хочу обновить информацию о целевом листе в следующей пустой строке.

Проблема ниже: когда (Set copyRow = wsSource.Range (namesArr (i, 1)). CompleteRow) пытается скопировать, я получаю «Определяемую приложением или объектом ошибку».

Заранее благодарю за помощь.

Что я пробовал: 

 Private Sub myProc ()

Dim wb As Workbook
Dim wsSource как рабочий лист
Dim wsNames как рабочий лист

Установить wb = ThisWorkbook
Установите wsSource = wb.Worksheets («Продажи»).
Установите wsNames = wb.Worksheets ("Имена")

Тусклые именаArr ()
namesArr = wsNames.Range ("A2: A" & wsNames.Ячейки (wsNames.Rows.Count, «A»). End (xlUp) .Row) .Value

Тусклый я как долго
Dim currLastRow As Long


Application. ScreenUpdating = False
Dim copyRow As Range

Для i = LBound (namesArr, 1) To UBound (namesArr, 1)

   С wb.Worksheets (namesArr (i, 1))

     Установите copyRow = wsSource.Range (namesArr (i, 1)). CompleteRow

     currLastRow = .Cells (.Rows.Count, "A"). End (xlUp) .Row

      .Rows (currLastRow + 1) .Value = copyRow.Value2

   Конец с

Далее я

Application.ScreenUpdating = True

Конечный переводник

Копирование строк между листами на основе текстового значения (Microsoft Excel)

Мэриан задается вопросом, есть ли способ скопировать всю строку с одного рабочего листа на другой, основываясь на наличии определенного текстового значения («да») в столбце E строки. Vlookup, похоже, не подходит для такого типа оценки.

Если вам нужно выполнять задачи такого типа время от времени, вы можете просто использовать возможности фильтрации Excel. Включите фильтр и отфильтруйте данные, чтобы отображались только те строки, в которых в столбце E указано «да». Скопируйте видимые строки и вставьте их в любой рабочий лист, который вы хотите.

Если вам нужно выполнять задачу чаще, вы можете создать макрос, чтобы сделать копирование несложным. Следующий макрос проверяет первые 1000 строк Sheet1 и копирует на Sheet2 любые строки, содержащие «да» в столбце E.

Дополнительная копия Да ()
    Dim c As Диапазон
    Dim j как целое число
    Тусклый источник как рабочий лист
    Тусклый целевой как рабочий лист

    'При необходимости измените обозначения рабочего листа
    Установите Source = ActiveWorkbook.Worksheets ("Sheet1")
    Установить цель = ActiveWorkbook.Worksheets ("Sheet2")

    J = 1 'Начать копирование в строку 1 целевого листа
    Для каждого c In Source. Range ("E1: E1000") 'Сделайте 1000 строк
        Если c = "yes" Тогда
           Источник.Строки (c.Row) .Copy Target.Rows (j)
           j = j + 1
        Конец, если
    Следующий c
Конец подписки

Обратите внимание, что макрос перезапишет любую информацию, уже находящуюся на Sheet2. Он также не является «динамическим», что означает, что если значения в столбце E изменяются и вы хотите, чтобы строки в Sheet2 отражали эти изменения, макрос не поможет.

Дополнительную информацию об автоматическом копировании информации с одного рабочего листа на другой можно найти на этом веб-сайте:

https: // www.mrexcel.com/forum/excel-questions/602635-
автоматически перемещать целые строки один лист в
другой-лист-же-workbook.html

Несмотря на то, что URL-адрес здесь отображается в трех строках, все это один URL-адрес. Убедитесь, что вы загрузили все это в свой браузер.

ExcelTips — ваш источник экономичного обучения Microsoft Excel.
Этот совет (13399) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365.

Автор Биография

Аллен Вятт

Аллен Вятт — всемирно признанный автор, автор более чем 50 научно-популярных книг и многочисленных журнальных статей. Он является президентом Sharon Parq Associates, компании, предоставляющей компьютерные и издательские услуги. Узнать больше о Allen …

Перекрестная ссылка на автоматический номер

Word позволяет добавлять автоматическую нумерацию к различным элементам документа, например к заголовкам.Если вы хотите …

Узнать больше

Печать без открытия

Хотите распечатать одну или несколько книг без необходимости открывать файл? Это легко сделать, если положиться на …

Узнать больше

Понимание структуры while … Wend

Одной из основных структур программирования, используемых в VBA, является структура While … Wend. Эта структура помогает сделать …

Узнать больше

целых строк и столбцов в Excel VBA

В этом примере показано, как выбрать целых строк и столбцов в Excel VBA .Вы готовы?

Поместите командную кнопку на лист и добавьте следующие строки кода:

1. Следующая строка кода выделяет весь лист.

Ячейки. Выбрать

Примечание: поскольку мы разместили нашу командную кнопку на первом листе, эта строка кода выбирает весь первый лист. Чтобы выбрать ячейки на другом листе, вы должны сначала активировать этот лист. Например, следующие строки кода выбирают весь второй лист.

Рабочие листы (2). Активировать
Рабочие листы (2) .Клетки. Выбрать

2. Следующая строка кода выбирает второй столбец.

Колонны (2). Выбрать

3. Следующая строка кода выбирает седьмую строку.

рядов (7). Выбрать

4. Чтобы выбрать несколько строк, добавьте строку кода, например:

рядов («5: 7»). Выберите

.

5. Чтобы выбрать несколько столбцов, добавьте строку кода, например:

Колонны («B: E»).Выбрать

6. Будьте осторожны, чтобы не перепутать свойства Row s и Column s со свойствами Row и Column. Свойства Rows и Columns возвращают объект Range. Свойства Row и Column возвращают одно значение.

Кодовая строка:

Ячейки MsgBox (5, 2). Ряд

результат:

7. Выберите ячейку D6. Следующая строка кода выбирает всю строку активной ячейки.

ActiveCell.Целый ряд. Выбрать

.

Примечание: рамка только для иллюстрации.

8. Выберите ячейку D6. Следующая строка кода вводит значение 2 в первую ячейку столбца, содержащего активную ячейку.

ActiveCell.EntireColumn.Cells (1) .Value = 2

Примечание: рамка только для иллюстрации.

9. Выберите ячейку D6. Следующая строка кода вводит значение 3 в первую ячейку строки под строкой, содержащей активную ячейку.

ActiveCell.EntireRow.Offset (1, 0) .Cells (1) .Value = 3

Примечание: рамка только для иллюстрации.

Поиск значения в столбце и копирование строки на новый лист для всех совпадающих значений

В этом руководстве Excel объясняется, как написать макрос для поиска значения в столбце и копирования совпадающих строк на новый лист в Excel 2003 и более ранних версиях (со снимками экрана и пошаговыми инструкциями).

Вопрос: Как в Microsoft Excel 2003 / XP / 2000/97 написать макрос, который будет искать строку во всем столбце.Если строка найдена, она скопирует всю строку на новый лист в том же файле?

Например,

Столбец E содержит все виды строк. Я ищу в этом столбце ячейку с надписью «Почтовый ящик». Когда строка «Почтовый ящик» найдена, соответствующая строка должна быть скопирована на новый лист.

Ответ: Давайте рассмотрим пример.

Загрузить электронную таблицу Excel (как показано ниже)

В нашей электронной таблице мы создали кнопку на Sheet1 под названием «Искать строку».Когда пользователь нажимает на эту кнопку, запускается макрос с именем SearchForString . Этот макрос будет искать значения в столбце E, ища «Почтовый ящик». Когда строка «Почтовый ящик» найдена в столбце E, соответствующая строка будет скопирована на Sheet2.

Когда макрос будет завершен, появится указанное выше окно сообщения.

Если вы просмотрите Sheet2, вы увидите, что скопированы только строки, в которых столбец E содержит строку «Mail Box».

Вы можете нажать Alt + F11 , чтобы просмотреть код VBA.

Код макроса

Код макроса выглядит так:

 Sub SearchForString ()

   Dim LSearchRow как целое число
   Dim LCopyToRow как целое число

   При ошибке GoTo Err_Execute

   'Начать поиск в строке 4
   LSearchRow = 4

   'Начать копирование данных в строку 2 в Sheet2 (переменная счетчика строк)
   LCopyToRow = 2

   Пока Len (Range ("A" & CStr (LSearchRow)). Value)> 0

      'Если значение в столбце E = «Почтовый ящик», скопируйте всю строку в Sheet2
      Если Range ("E" & CStr (LSearchRow)).Значение = "Почтовый ящик" Тогда

         'Выберите строку в Sheet1 для копирования
         Строки (CStr (LSearchRow) & ":" & CStr (LSearchRow)). Выберите
         Selection.Copy

         'Вставить строку в Sheet2 в следующей строке
         Таблицы ("Лист2"). Выберите
         Строки (CStr (LCopyToRow) & ":" & CStr (LCopyToRow)). Выберите
         ActiveSheet.Paste

         'Переместить счетчик в следующую строку
         LCopyToRow = LCopyToRow + 1

         'Вернитесь к Sheet1, чтобы продолжить поиск
         Таблицы ("Лист1"). Выберите

      Конец, если

      LSearchRow = LSearchRow + 1

   Wend

   'Позиция в ячейке A3
   Заявка.CutCopyMode = False
   Диапазон ("A3"). Выбрать

   MsgBox «Все совпадающие данные скопированы».

   Выход из подводной лодки

Err_Execute:
   MsgBox «Произошла ошибка».

Конечный переводник

СОВЕТ: Этот макрос найдет только те строки, в которых вся ячейка соответствует критериям поиска, но не текст внутри ячейки. Другими словами, он не найдет строки, в которых часть ячейки соответствует критериям поиска. Если пользователь хочет найти часть ячейки, совпадающую с текстом, он может использовать функцию InStr в приведенном выше коде:

И заменить:

 Если диапазон ("E" & CStr (LSearchRow)).Значение = "Почтовый ящик" Тогда

с:

 Если InStr (1, Range ("E" & CStr (LSearchRow)). Value, "Mail Box")> 0, то

Ячейки и диапазоны Excel — Копирование

Копирование

Excel> Диаграммы> Код VBA> Копирование
Excel> Иллюстрации> Код VBA> Копирование
Word> Абзацы> Код VBA> Копирование

Копирование и вставка одно значение

Самый интуитивно понятный способ скопировать данные из одной ячейки в другую:

 Диапазон («A2»).Выберите 
 Selection.Copy 
 Range ("D2"). Выберите 
 ActiveSheet.Paste

Один из наиболее важных аспектов программирования VBA — помнить, что вам не нужно фактически выбирать объект, которым вы хотите управлять.
Таким образом, код можно изменить на следующий.

 Диапазон («A2»). Скопируйте диапазон 
 («D2»). Выберите 
 ActiveSheet.Paste

В VBA всегда есть несколько способов сделать то же самое.
Код можно сократить до двух строк с помощью метода специальной вставки.

 Диапазон ("A2"). Скопируйте 
 Диапазон ("D2"). PasteSpecial Paste: = xlValues ​​

Фактически это действие можно объединить в одну строку.
Это возможно, потому что метод Copy может принимать необязательный аргумент, который может представлять диапазон, в который нужно копировать. Следующие две строки эквивалентны.

 Диапазон («A2»). Место назначения копирования: = Диапазон («D2») 
 Диапазон («A2»). Копирование диапазона («D2»)

Это копирует содержимое ячейки «D2» и помещает их в активная ячейка.

 Диапазон ("D2"). Назначение копирования: = ActiveCell

Копирование данных между листами

Можно копировать данные между листами.
Это можно сделать, включив ссылку на конкретный рабочий лист. Если рабочий лист не указан, используется активный рабочий лист.

 Рабочие листы ("Лист1"). Диапазон ("A2"). Копировать рабочие листы ("Лист2"). Диапазон ("D2")

Обратите внимание, что вам не нужно активировать необходимые рабочие листы.
Следующая строка кода копирует весь блок данных на лист Sheet2.

 Диапазон ("A2"). CurrentRegion.Copy Sheets ("Sheet2"). Range ("D2")

Копирование данных между рабочими книгами

Можно копировать данные между рабочими листами.
Это можно сделать, включив ссылку на конкретный рабочий лист. Если рабочий лист не указан, используется активный рабочий лист.

 Рабочие книги ("Wbk1.xls". Рабочие листы ("Sheet1"). Диапазон ("A2"). Копирование рабочих книг ("Wbk2.xls"). Рабочие листы ("Sheet2"). Диапазон ("D2")

Иногда бывает трудно понять длинные строки кода.
Другой способ выполнить ту же задачу — использовать переменные для хранения необходимых объектов диапазона.

 Dim rgeCopyRange As Range 
 Dim rgeToRange As Range 
 Установить rgeCopyRange = Workbooks ("Wbk1.xls"). Worksheets ("Sheet1"). Range ("A2") 
 Set rgeToRange = Workbooks ("Wbk2.xls"). Рабочие листы ("Sheet2"). Range ("D2") 
 rgeCopyRange.Copy rgeToRange

Отмена режима копирования

При копировании (или вырезании) диапазона ячеек вокруг области появляется черная пунктирная линия, чтобы помочь идентифицировать это.
Это можно удалить, установив для свойства CutCopy значение False.

 Application.CutCopyMode =  xlCutCopyMode  .False

Если вы не измените это свойство обратно на false, то диапазон ячеек, который был скопирован последним, будет продолжать мигать на экране.

 Range ("A2"). Скопируйте 
 Range ("D2"). Выберите 
 ActiveSheet.Paste 
 Application.CutCopyMode =  xlCutCopyMode  .False

Если вы используете одну строку, сбрасывать это свойство не нужно. заявление для копирования и вставки.

 Диапазон ("A2"). Копировать диапазон ("D2")

Копирование диапазонов как изображений

для копирования выбранного диапазона рабочего листа как изображения

 Selection.CopyPicture Внешний вид: = xlPictureAppearance.xlScreen, Format: = xlCopyPictureFormat.xlPicture

Замораживание и размораживание ссылок при копировании 
 Public arFormulas () As Variant 
 Public Sub Freeze () 
 arFormulas = Selection.Formula 
 End Sub 
 
 Public Sub UnFreeze (Not) 
 Public Sub UnFreeze (Not) 
 = 0 Затем 
 Вызовите MsgBox ("Ошибка: формулы не были скопированы.Пожалуйста, используйте сначала процедуру Freeze. ") 
 Exit Sub 
 End If 
 
 Selection.Resize (UBound (arFormulas, 1), UBound (arFormulas, 2)). Value = arFormulas 
 End Sub

© 2020 Better Solutions Limited. Все права защищены. © 2020 Better Solutions Limited TopPrevNext

Копирование данных с одного листа на другой с помощью VBA — Панели мониторинга Excel VBA

Копирование данных, соответствующих критериям

Пользователи Excel — прагматичная группа и растут, используя оператор IF в каждый день использую Excel.Поэтому, когда дело доходит до VBA, естественно, что лучший способ переместить данные из одного места в другое — это проверить каждую ячейку, чтобы увидеть, соответствует ли она условию, а затем переместить всю строку. Ниже приведен типичный пример кода, который я вижу на форумах Excel;

Option Explicit
Sub Test ()
Dim i As Integer

Application.ScreenUpdating = False

Для i = от 2 до 101

Если Range («B» & i) .Value = «Ford», то

Диапазон («B» и i) .EntireRow.Copy
листов («Sheet2»).Выберите диапазон
(«A1»). Конец (xlDown) .Offset (1, 0). Выберите
Selection.PasteSpecial xlPasteValues ​​
Sheets («Sheet1»). Выберите

Конец, если

Далее я

Application.ScreenUpdating = True
End Sub

Я, вероятно, ввел слишком много строк, но я сделал предположение, что записывающее устройство использовалось для генерации основной части кода. Проблема с приведенным выше VBA заключается в том, что по мере экспоненциального роста списка информации время, необходимое для выполнения процесса, уменьшается в зависимости от размера набора данных.Если список состоит из 100 строк, Excel должен выполнить 100 действий, если все элементы соответствуют критериям. Можно утверждать, что в приведенном выше примере Excel должен выполнить 200 действий: задать вопрос 100 раз, потенциально выполнить действие еще 100 раз.

Простое решение

Проще задать один вопрос Excel и выполнить одно действие Excel для всего набора данных. Это можно сделать, опираясь на предыдущие уроки из раздела Autofiler с VBA на сайте.В приведенном выше примере очень простым способом перемещения данных будет следующая конструкция VBA;

Option Explicit
Sub Filter1 () ‘Excel VBA для использования автофильтра, затем скопируйте

Диапазон («A1: A101»). Автофильтр 1, «Ford» Диапазон
(«A1: A101»). Копировать Sheet2.Range («A» & Rows.Count) .End (xlUp) (2)
Range (» A1 «). Автофильтр ‘Выкл. Автофильтром

Конец подписки

Этот метод полезен, если вы хотите регулярно помещать информацию в сводную таблицу.

No related posts.

Share :

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

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