How To Delete A Row In Excel Vba

    
      Sub DeleteRowVBA()
        Dim rowNum As Integer
        Dim lastRow As Integer
        Dim rng As Range
        
        ' Set the row number you want to delete
        rowNum = 5
        
        ' Get the last row in the worksheet
        lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
        
        ' Check if the row number is within the range of the worksheet
        If rowNum > 0 And rowNum <= lastRow Then
          ' Set the range of the row to be deleted
          Set rng = Rows(rowNum)
          
          ' Delete the row
          rng.Delete Shift:=xlUp
          
          MsgBox "Row " & rowNum & " is deleted successfully."
        Else
          MsgBox "Invalid row number."
        End If
      End Sub
    
  

In the above example, we are deleting a specific row using VBA in Excel. Here is a detailed explanation of how the code works:

1. We start by declaring variables `rowNum`, `lastRow`, and `rng` to store the row number to be deleted, the last row number in the worksheet, and the range of the row to be deleted, respectively.

2. We set the `rowNum` variable to the desired row number to be deleted. In this example, we have set it to 5.

3. We use the `ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row` statement to find the last non-empty row in column 1 (A) of the active sheet. This gives us the value of the `lastRow` variable.

4. We check if the `rowNum` is within the range of the worksheet, i.e., it should be greater than 0 and less than or equal to `lastRow`.

5. If the `rowNum` is within the range, we use the `Set` statement to set the `rng` variable to the range of the row to be deleted. In this example, we are working with a single row, so we only need to provide the row number.

6. We use the `rng.Delete Shift:=xlUp` statement to delete the row from the worksheet. The `Shift:=xlUp` argument shifts the cells above the deleted row upwards to fill the gap.

7. Finally, we display a message box indicating that the row has been successfully deleted. If the `rowNum` is invalid, we display an error message instead.

Same cateogry post

Leave a comment