Delete rows with vba

[mmn1000]

Greetings and good time dear teachers:

In an Excel file that has entered a series of specifications and also has a row, if we want to enter a specific and fixed cell, when we enter the row number, that desired row will be deleted and also pasted in another sheet, of course with VBA.

What formula should be used?

 

[MARK858]

if we want to enter a specific and fixed cell

Which cell?
What is the sheet name of the sheet the row is being copied from?

and also pasted in another sheet

What is the sheet name of the sheet the row is being pasted to?
in which row, the next available row? if the next available row what column is that row based on?

What formula should be used?

VBA is code not a formula

 

[mmn1000]

If we have a table.
For example, 100 rows from the range of a3 to m100, how can it be deleted in cell A1 when a row number is written using row coding?

and be pasted in sheet two

 

[MARK858]

Please answer all the questions asked, so far you have only answered possibly 2 of the 5 questions and with one of the answers do you mean the destination sheet is actually named sheet two or is it actually named Sheet2?

how can it be deleted in cell A1

Do you mean A1 one is the cell holding the number to be used?

 

[mmn1000]

We have two sheets in the file, sheet1 and sheet2.

In cell A1, enter the number of the row to be deleted.

 

[MARK858]

See where you get with the code below

Sub MoveRows()
    Dim i As Long
    Application.ScreenUpdating = False

    With Sheets("Sheet1")
        i = .Cells(1, "A").Value
        .Rows(i).Copy Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1)
        .Rows(i).Delete
    End With

    Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

@mmn1000
For your future posts, please use the standard forum font, colour etc unless there is a specific need to highlight some particular thing(s). It is covered by the first sentence of #14 of the Forum Rules

 

[mmn1000]

See where you get with the code below

 Sub MoveRows() Dim i As Long Application.ScreenUpdating = False With Sheets("Sheet1") i = .Cells(1, "A").Value .Rows(i).Copy Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1) .Rows(i).Delete End With Application.ScreenUpdating = True End Sub

Thank you for your beautiful code
There is a small problem here, this code uses the rows of Excel itself, if I want it to use the rows that I entered in the table.

 

[MARK858]

Based on your image. The table name in red needs changing to your actual table name as you haven't told us what it is.

Sub MoveRows()
    Dim i As Long
    Application.ScreenUpdating = False

    With Sheets("Sheet1")
        i = .Cells(1, "A").Value + 3
        Intersect(.Rows(i), .ListObjects("Table1").DataBodyRange).Copy Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1)
        Intersect(.Rows(i), .ListObjects("Table1").DataBodyRange).Delete
    End With

    Application.ScreenUpdating = True
End Sub

 

[johnnyL]

This is what I came up with that also allows for the table location to be changed:

Sub CopyDeleteTableRow()
'
    Dim MyTable                 As ListObject
    Dim MyTableDataStartRow     As Long, TableRowToCopyDelete   As Long
    Dim wsDestination           As Worksheet, wsSource          As Worksheet
'
    Set wsSource = Sheets("Sheet1")                                                                                 ' <--- Set this to the name of sheet containing the table
    Set wsDestination = Sheets("Sheet2")                                                                            ' <--- Set this to the name of sheet that you will copy row to
    Set MyTable = wsSource.ListObjects(1)                                                                           ' <--- Set this to the proper Table #
'
    MyTableDataStartRow = MyTable.Range.Cells(1, 1).Row                                                             ' Get the Row # of the table header
'
    TableRowToCopyDelete = wsSource.Range("A1").Value                                                               ' Get the table row # to copy/delete
'
    With wsSource.Rows(TableRowToCopyDelete + MyTableDataStartRow)
        .Copy wsDestination.Range("A" & wsDestination.Range("A" & wsDestination.Rows.Count).End(xlUp).Row + 1)      '   Copy the row # to the Destination sheet
        .Delete                                                                                                     '   Delete the row # from the table
    End With
End Sub