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?

 

[johnnyL]

After looking at @MARK858 code I decided to change 1 line of code so that only the Table values are copied/deleted:

Sub CopyDeleteTableRowV2()
'
    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)
    With Intersect(wsSource.Rows(TableRowToCopyDelete + MyTableDataStartRow), MyTable.DataBodyRange)
        .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

 

[Alex Blakenburg]

Or for a single row in a Table just:

With MyTable.ListRows(TableRowToCopyDelete).Range

 

[mmn1000]

Thanks for the guide my dear friends
But the codes you sent did not work
The code I use is the code below

Sub DeleteRow()
    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
    With ActiveSheet
        .Select
        Firstrow = .UsedRange.Cells(1).Row
        Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
        For Lrow = Lastrow To Firstrow Step -1
            With .Cells(Lrow, "A")
                If Not IsError(.Value) Then
                    If .Value = Range("B1") Then .EntireRow.Delete
                End If
            End With
        Next Lrow
    End With
End Sub

My code does not include the second part, i.e. copying to the second sheet
How do I modify this code?

 

[MARK858]

The code you posted does not refer to a table, it refers to a normal range and doesn't use A1 for the row to move/delete. It uses B1.

The codes everyone have posted work with tables. You have to amend them for your table name in my code and Alex's (JohnnyL's works on the first table created on a sheet), if it is an actual table and not a normal range and you want what you asked for in your earlier posts.

 

[MARK858]

If what you want is to loop though a Table comparing column 1 to cell B1 and copying if the values match and then deleting the row then try the code below (again change the Tables name to match your Tables name)

Sub LoopTable()

    Dim LstCell As Long, i As Long
    LstCell = Sheets("Sheet1").ListObjects("Table1").DataBodyRange.Rows.Count

    For i = LstCell To 1 Step -1

        If Sheets("Sheet1").ListObjects("Table1").DataBodyRange.Cells(i, 1).Value = _
                    Sheets("Sheet1").Cells(1,"B").Value Then

            With Sheets("Sheet1").ListObjects("Table1").ListRows(i).Range
                .Copy Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1)
                .Delete
            End With
  
        End If
    Next
      
End Sub