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.