vba excel宏选择多行

excel macro to select multiple row

提问by Minnie

I have excel macro to select a row to cut and paste to next sheet. Now I want to select multiple row at one time to cut and paste to next sheet, then go back to previous sheet to delete the blank rows that were cut. The code I have for the single row cut and paste is follows:

我有 excel 宏来选择要剪切并粘贴到下一张纸的行。现在我想一次选择多行剪切并粘贴到下一张纸，然后返回上一张纸以删除被剪切的空白行。我的单行剪切和粘贴代码如下：

Sub CutPasteRows()
Dim iLastRow As Integer


   'select the first cell to intiate script
    txtRowNum = Selection.Row
    'select the row
    Rows(txtRowNum).EntireRow.Select
    Selection.Cut

    'if the active worksheet is the last one (no next worksheet), display error.
    If ActiveSheet.Index = Worksheets.Count Then
       MsgBox ("There are no next worksheet")
    Else
        ActiveSheet.Next.Select
        iLastRow = ActiveSheet.UsedRange.Rows.Count

        'if the workshet is blank, start with the first row. Otherwise, add 1 to the last row
        If Cells(1, 1).Value = "" And iLastRow = 1 Then
            iLastRow = 1
        Else
            iLastRow = iLastRow + 1
        End If

        'Paste row
        Rows(iLastRow).EntireRow.Select
        ActiveSheet.Paste

        'Go back to the last worksheet
        ActiveSheet.Previous.Select
        Rows(txtRowNum).EntireRow.Select
        Selection.Delete
    End If
End Sub

Any help is appreciated.

任何帮助表示赞赏。

Thanks

谢谢

回答by Amal Sirisena

If you select multiple rows at once, the Selection property will return a Range object. Using this Range object you should be able to cut and paste the selected rows to the next worksheet and then delete them from the previous worksheet.

如果您一次选择多行，Selection 属性将返回一个 Range 对象。使用此 Range 对象，您应该能够将所选行剪切并粘贴到下一个工作表，然后从上一个工作表中删除它们。

I made a quick change to your code which I think should get you started on the right path:

我对您的代码进行了快速更改，我认为这应该能让您走上正确的道路：

Sub CutPasteRows()
 Dim iLastRow As Integer

 'Cut entire selection'
 Selection.Cut

 'if the active worksheet is the last one (no next worksheet), display error.'
  If ActiveSheet.Index = Worksheets.Count Then
   MsgBox ("There are no next worksheet")
  Else
    ActiveSheet.Next.Select
    iLastRow = ActiveSheet.UsedRange.Rows.Count

  'if the worksheet is blank, start with the first row. Otherwise, add 1 to the last row'
    If Cells(1, 1).Value = "" And iLastRow = 1 Then
        iLastRow = 1
    Else
        iLastRow = iLastRow + 1
    End If

    'Paste row'
    Rows(iLastRow).EntireRow.Select
    ActiveSheet.Paste

    'Go back to the last worksheet and delete selection'
    ActiveSheet.Previous.Select
    Selection.Delete
End If

Selection property

选择属性

Range object

范围对象

回答by Lance Roberts

You just need to Union the Rows you need.

你只需要联合你需要的行。

Like this:

像这样：

Set workingRange = Application.Union(workingRange, newRange)

Of course, that means using range objects and not row numbers.

当然，这意味着使用范围对象而不是行号。