首页 文章

excel宏选择多行

提问于
浏览 次
0

我有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

任何帮助表示赞赏 .

谢谢

excel vba excel-vba

2 回答

  • 0

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

    我快速更改了您的代码，我认为应该让您开始正确的道路：

    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

    回复于
  • 1

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

    像这样：

    Set workingRange = Application.Union(workingRange, newRange)

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

    回复于

相关问题