CODEWITHSUNDEEP
vbaexcel
Minnie
Minnie

Reputation: 1

excel macro to select multiple row

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:

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

Upvotes: 0

Views: 14403

Answers (2)

Amal Sirisena
Amal Sirisena

Reputation: 1479

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.

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

Upvotes: 1

Lance Roberts
Lance Roberts

Reputation: 22842

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.

Upvotes: 0

Related Questions