Excel VBA: Delete Row on Another Sheet

Get FREE Advanced Excel Exercises with Solutions!

Looking for ways to delete rows on another sheet using Excel VBA? Then, this is the right place for you.

Deleting rows in Excel using VBA can be a powerful tool when working with large datasets. VBA is an abbreviation for Visual Basic for Applications, and it is a programming language that allows you to automate tasks in Excel. One of the common tasks is to delete rows on another sheet in Excel. This can be useful when you have a lot of data that needs to be cleaned up, or when you want to remove duplicate data from your worksheet.

Manually deleting rows on another sheet can be a tedious process, especially if you have a lot of data to go through. With VBA, you can automate this process and save yourself a lot of time and effort. VBA allows you to perform complex operations on your data, such as finding and deleting specific rows based on a certain condition or criteria.

In this article, we will show you how to delete rows on another sheet in Excel using VBA. We will cover different scenarios and techniques, such as deleting duplicate rows, deleting the last row, clearing row contents, and deleting rows based on a specific condition. With these techniques, you can easily clean up your data and streamline your workflow.


How to Launch Visual Basic Editor in Excel?

To Launch Visual Basic Editor, we need to click on Visual Basic under the Developer tab.

Opening Visual Basic Window

Then, Insert a module to write the code. Finally, inside the module, we can write suitable codes to get our desired output. We have to repeat this process for adding each of the new Macros. (If you are using VBA for the first time, you may need to add the Developer tab inside the ribbon Excel)

Inserting Module Inside the Visual Basic Window


How to Delete a Row with Excel VBA?

This section shows you how to delete a row in Excel in general. The code prompts the user to input a row number to delete in the active sheet and then deletes the entire row. A message box is displayed to confirm which row was deleted.

Steps:

  • First, we need to insert a module as stated earlier.
  • Then, we need to write the following code inside the module.

Code Image of How to Delete a Row with Excel VBA

Code Syntax:

Sub DeleteRowInCurrentSheet()
Dim rowNum As Long'Get the row number to be deleted from user input
rowNum = InputBox("Enter the row number to delete:")
'Delete the row in the active sheet
Rows(rowNum).EntireRow.Delete
MsgBox "Row no " & rowNum & " is deleted from the active sheet."
End Sub

Code Breakdown:

Sub DeleteRowInCurrentSheet()

This is a Subroutine in VBA used to delete a row in the current sheet.

Dim rowNum As Long

Declares a variable to store the row number to be deleted as a Long data type.

rowNum = InputBox("Enter the row number to delete:")

Prompts the user to enter the row number to be deleted through an InputBox and assigns it to the variable “rowNum”.

Rows(rowNum).EntireRow.Delete

Deletes the entire row specified by the “rowNum” variable.

MsgBox "Row no " & rowNum & " is deleted from the active sheet."

Displays a message box informing the user that the row has been deleted.

End Sub

Ends the definition of the subroutine.

  • Consequently, we need to run the code.
  • You will get the output. Inside the MsgBox, click OK to end the process.

Output of How to Delete Row with Excel VBA

  • Finally, a before-after scenario of this example is given below:

Before-After Scenario of How to Delete Row with Excel VBA


Excel VBA: Delete Row on Another Sheet (10 Examples)

Now, you are going to learn how to delete row on another sheet using Excel VBA. (Not ActiveSheet). To demonstrate these 5 examples we have taken a dataset named “Yearly Evaluations of Ten Students” where both individual and total marks of 10 different students in 5 different subjects are visible.

Sample Dataset

Read More: Excel VBA to Delete Entire Row


1. Delete Any Row on Another Sheet

In this example, the code allows the user to enter the name of a worksheet and the row number to be deleted in that worksheet. It then attempts to set the target sheet to the sheet with the given name and deletes the specified row. Thus, it will delete row on another sheet using Excel VBA. If the user enters an invalid sheet name, it displays an error message.

Steps:

  • First, we need to insert a module as stated earlier.
  • Then, we need to write the following code inside the module.

Code Image of Delete Any Row on Another Sheet

Code Syntax:

Sub DeleteRowInOtherSheet()
Dim sheetName As String
Dim rowNum As Long
Dim targetSheet As Worksheet'Get the sheet name and row number to be deleted from user input
sheetName = InputBox("Enter the name of the sheet to delete rows from:")
If sheetName = "" Then
MsgBox "Please enter a sheet name"
Exit Sub
End If
'Attempt to set the target sheet to the sheet with the given name
On Error GoTo ErrorHandler
Set targetSheet = ThisWorkbook.Worksheets(sheetName)
rowNum = InputBox("Enter the row number to delete:")
'Delete the row in the target sheet
targetSheet.Rows(rowNum).Delete
MsgBox "Row no " & rowNum & " is deleted from the sheet: " & sheetName
Exit Sub
ErrorHandler:
MsgBox "Please enter a valid sheet name."
End Sub

Code Breakdown:

Sub DeleteRowInOtherSheet()

A sub procedure is declared with the name DeleteRowInOtherSheet.

Dim sheetName As String

Declares a variable called sheetName as a string data type.

Dim rowNum As Long

This line Declares a variable called rowNum as a long data type.

Dim targetSheet As Worksheet

Declares a variable called targetSheet as a Worksheet data type.

sheetName = InputBox("Enter the name of the sheet to delete rows from:")

Prompts the user to enter the name of the sheet to delete rows from and assigns the entered value to the sheetName variable.

If sheetName = "" Then

Checks if sheetName variable is empty.

MsgBox "Please enter a sheet name"

Displays a message box with the message “Please enter a sheet name” if sheetName is empty.

Exit Sub

Exits the sub procedure.

End If

Ends the If statement.

On Error GoTo ErrorHandler

Sets up an error handler to jump to the ErrorHandler label if an error occurs.

Set targetSheet = ThisWorkbook.Worksheets(sheetName)

Sets the targetSheet variable to the sheet object that matches the sheetName value entered by the user.

rowNum = InputBox("Enter the row number to delete:")

Prompts the user to enter the row number to delete and assigns the entered value to the rowNum variable.

targetSheet.Rows(rowNum).Delete

Deletes the row number entered by the user in the targetSheet worksheet.

MsgBox "Row no " & rowNum & " is deleted from the sheet

” & sheetName: Displays a message box that confirms the row number and sheet name that were deleted.

Exit Sub

Exits the sub procedure.

ErrorHandler

A label that is used as a jump target if an error occurs.

MsgBox "Please enter a valid sheet name."

Displays a message box with the message “Please enter a valid sheet name.” if an error occurs.

End Sub

Ends the definition of the subroutine.

  • Consequently, we need to run the code.
  • You will get the output. Inside the MsgBox, click OK to end the process.

Output of Delete Any Row on Another Sheet


2. Delete Multiple Rows in Another Sheet

In this example, this VBA code prompts the user to input the name of a sheet, then attempts to set that sheet as the target. It sets a specific range of rows (B4:F6) to be deleted in the target sheet. It then loops through each row in the range and deletes it from the target sheet. If an invalid sheet name is entered, the code displays an error message.

Steps:

  • First, we need to insert a module as stated earlier.
  • Then, we need to write the following code inside the module.

Code Image of Delete Multiple Rows in Another Sheet

Code Syntax:

Sub DeleteMultipleRowsInOtherSheet()
Dim sheetName As String
Dim rowRange As Range
Dim targetSheet As WorksheetsheetName = InputBox("Enter the name of the sheet to delete rows from:")
If sheetName = "" Then
MsgBox "Please enter a sheet name"
Exit Sub
End If
'Attempt to set the target sheet to the sheet with the given name
On Error GoTo ErrorHandler
Set targetSheet = ThisWorkbook.Worksheets(sheetName)
Set rowRange = Range("B4:F6")
'Loop through each row in the range and delete it from the target sheet
Dim i As Long
For i = rowRange.Rows.Count To 1 Step -1
targetSheet.Rows(rowRange.Rows(i).Row).Delete
Next i
MsgBox "Desired Rows are deleted."
Exit Sub
ErrorHandler:
MsgBox "Please enter a valid sheet name."
End Sub

Code Breakdown:

Sub DeleteMultipleRowsInOtherSheet() 

Defines the name of the subroutine.

Dim sheetName As String 

Declares a variable named ‘sheetName’ to store the name of the target sheet.

Dim rowRange As Range 

This line Declares a variable named ‘rowRange’ to store the range of rows to be deleted.

Dim targetSheet As Worksheet 

Declares a variable named ‘targetSheet’ to store the worksheet object of the target sheet.

sheetName = InputBox("Enter the name of the sheet to delete rows from:") 

Prompts the user to input the name of the sheet to delete rows from and stores it in the variable ‘sheetName’.

If sheetName = "" Then 

Checks if the user input a sheet name. If not, displays a message box asking the user to enter a sheet name and exits the subroutine.

On Error GoTo ErrorHandler 

Sets an error handler to jump to a specified label in case an error occurs.

Set targetSheet = ThisWorkbook.Worksheets(sheetName) 

Assigns the worksheet object of the sheet with the name specified by the user to the variable ‘targetSheet’.

Set rowRange = Range("B4:F6") 

Here, this line Assigns the range of rows to be deleted to the variable ‘rowRange’.

Dim i As Long 

Declares a variable named ‘i’ to use as a counter variable in the loop.

For i = rowRange.Rows.Count To 1 Step -1 

Loops through each row in the range ‘rowRange’ in reverse order.

targetSheet.Rows(rowRange.Rows(i).Row).Delete 

Deletes the current row in the loop from the target sheet.

Next i 

Goes to the next iteration of the loop.

MsgBox "Desired Rows are deleted." 

Displays a message box informing the user that the desired rows are deleted.

Exit Sub 

Exits the subroutine.

ErrorHandler: 

Specifies the label to jump to in case of an error.

MsgBox "Please enter a valid sheet name." 

Displays a message box informing the user that they need to enter a valid sheet name.

End Sub 

Ends the definition of the subroutine.

  • Consequently, we need to run the code.
  • You will get the output. Inside the MsgBox, click OK to end the process.

Output of Delete Multiple Rowa in Another Sheet

Read More: How to Delete Multiple Rows with VBA in Excel


3. Delete the Row in Another Sheet When the Condition Met

In this example, the code prompts the user to enter the name of a worksheet, and then deletes rows in that worksheet where the value in column F is less than 180. A message box is then displayed indicating the rows that were deleted. Thus, it will delete a row on another sheet using Excel VBA. If an invalid sheet name is entered, an error message is displayed. The code starts deleting rows from row 4, so the header row is not deleted.

Steps:

  • First, we need to insert a module as stated earlier.
  • Then, we need to write the following code inside the module.

Code Image of Delete Row in Another Sheet When Condition Met

Code Syntax:

Sub DeleteRowInOtherSheet()
Dim sheetName As String
Dim targetSheet As Worksheet
Dim lastRow As Long
Dim i As Long'Get the sheet name and target sheet
sheetName = InputBox("Enter the name of the sheet to delete rows from:")
If sheetName = "" Then
MsgBox "Please enter a sheet name"
Exit Sub
End If
On Error GoTo ErrorHandler
Set targetSheet = ThisWorkbook.Worksheets(sheetName)'Determine the last row in the target sheet
lastRow = targetSheet.Cells(targetSheet.Rows.Count, "B").End(xlUp).Row'Loop through each row in the target sheet and delete rows that meet the condition
For i = lastRow To 4 Step -1
If targetSheet.Cells(i, "F").Value < 180 Then
targetSheet.Rows(i).Delete
End If
Next i'Display a message box indicating the rows that were deleted
MsgBox "Rows meeting the condition have been deleted from the sheet: " & sheetName
Exit Sub
ErrorHandler:
MsgBox "Please enter a valid sheet name."
End Sub

Code Breakdown:

Sub DeleteRowInOtherSheet()

defines a subroutine named DeleteRowInOtherSheet.

Dim sheetName As String

This line declares a variable named sheetName as a String data type.

Dim targetSheet As Worksheet

declares a variable named targetSheet as a Worksheet data type.

Dim lastRow As Long

Here, this line declares a variable named lastRow as a Long data type.

Dim i As Long

Now, this declares a variable named i as a Long data type.

sheetName = InputBox("Enter the name of the sheet to delete rows from:")

prompts the user to enter the name of the sheet to delete rows from and assigns the input value to the sheetName variable.

If sheetName = "" Then

checks if the sheetName variable is empty.

MsgBox "Please enter a sheet name"

displays an error message if the sheetName variable is empty.

Exit Sub

exits the subroutine.

End If

ends the If statement.

On Error GoTo ErrorHandler

handles runtime errors by transferring control to the ErrorHandler section.

Set targetSheet = ThisWorkbook.Worksheets(sheetName)

sets the targetSheet variable to the worksheet with the name specified in the sheetName variable.

lastRow = targetSheet.Cells(targetSheet.Rows.Count, "B").End(xlUp).Row

finds the last row in the target sheet with data in column B and assigns the row number to the lastRow variable.

For i = lastRow To 4 Step -1

starts a loop that goes from the lastRow variable down to row number 4 with a step of -1 (i.e., looping from the bottom up).

If targetSheet.Cells(i, "F").Value < 180 Then

checks if the value in column F of the current row (i) is less than 180.

targetSheet.Rows(i).Delete

deletes the entire row if the condition in line 15 is true.

Next i

moves to the next row in the loop.

MsgBox "Rows meeting the condition have been deleted from the sheet: " & sheetName

displays a message box indicating that the rows meeting the condition have been deleted from the sheet specified in the sheetName variable.

Exit Sub

exits the subroutine.

ErrorHandler

handles runtime errors by displaying an error message box.

End Sub

Ends the definition of the subroutine.

  • We need to run the code.
  • You will get the output. Inside the MsgBox, click OK to end the process.

Output of Delete Row in Another Sheet When Condition Met

  • Finally, you can see a before-after scenario of this example below where the VBA code has deleted row on another sheet in Excel.

Before-After Scenario of Delete Row in Another Sheet When Condition Met


4. Delete Alternate Rows in Another Sheet

In this example, the code deletes alternate rows (every other row) in a specified worksheet based on user input for the worksheet name. If the worksheet name is invalid or blank, the code displays an error message. Once the deletion is completed, the code displays a message box to confirm the completion of the operation.

Steps:

  • First, we need to insert a module as stated earlier.
  • Then, we need to write the following code inside the module.

Code Image of Delete Alternate Rows in Another Sheet 

Code Syntax:

Sub DeleteAlternateRowsInOtherSheet()
Dim sheetName As String
Dim targetSheet As Worksheet
Dim rowNum As Long'Get the sheet name from user input
sheetName = InputBox("Enter the name of the sheet to delete alternate rows from:")
If sheetName = "" Then
MsgBox "Please enter a sheet name."
Exit Sub
End If'Attempt to set the target sheet to the sheet with the given name
On Error GoTo ErrorHandler
Set targetSheet = ThisWorkbook.Worksheets(sheetName)'Delete the alternate rows in the target sheet
For rowNum = targetSheet.Cells(targetSheet.Rows.Count, 2).End(xlUp).Row To 5 Step -2
targetSheet.Rows(rowNum).Delete
Next rowNumMsgBox "Alternate rows deleted from the sheet: " & sheetName
Exit Sub
ErrorHandler:
MsgBox "Please enter a valid sheet name."
End Sub

Code Breakdown:

Sub DeleteAlternateRowsInOtherSheet()

– This line is the beginning of the subroutine, which is named “DeleteAlternateRowsInOtherSheet”.

Dim sheetName As String

– Declares a variable called “sheetName” as a string data type.

Dim targetSheet As Worksheet

– This line declares a variable called “targetSheet” as a worksheet data type.

Dim rowNum As Long

– Here, this line declares a variable called “rowNum” as a long integer data type.

'Get the sheet name from user input

– This line is a comment.

sheetName = InputBox("Enter the name of the sheet to delete alternate rows from:")

– Next this line displays an input box to the user, asking them to enter the name of the sheet from which they want to delete alternate rows, and assigns the entered value to the “sheetName” variable.

If sheetName = "" Then

– This line begins an “If” statement, checking if the “sheetName” variable is empty.

MsgBox "Please enter a sheet name."

– Then, this line displays an error message to the user if they did not enter a sheet name.

Exit Sub

– This line exits the subroutine.

'Attempt to set the target sheet to the sheet with the given name

– After that, this line is a comment.

On Error GoTo ErrorHandler

– Sets an error handler to jump to the “ErrorHandler” subroutine if an error occurs.

Set targetSheet = ThisWorkbook.Worksheets(sheetName)

– Attempts to set the “targetSheet” variable to the worksheet with the name specified in the “sheetName” variable.

'Delete the alternate rows in the target sheet

– This line is a comment.

For rowNum = targetSheet.Cells(targetSheet.Rows.Count, 2).End(xlUp).Row To 5 Step -2

– Next, this line starts a “For” loop, where the “rowNum” variable is assigned a value starting from the last used row of column 2 (B) in the “targetSheet” worksheet, and then counting down by 2 until it reaches row 5.

targetSheet.Rows(rowNum).Delete

– Deletes the row specified by the current value of “rowNum” in the “targetSheet” worksheet.

Next rowNum

– Marks the end of the “For” loop.

MsgBox "Alternate rows deleted from the sheet: " & sheetName

– Displays a message box to the user indicating that alternate rows have been deleted from the specified sheet.

Exit Sub

– Exits the subroutine.

ErrorHandler:

– Marks the beginning of the “ErrorHandler” subroutine.

MsgBox "Please enter a valid sheet name."

– Displays an error message to the user if the sheet name they entered is not valid.

End Sub

– Marks the end of the subroutine.

  • Consequently, we need to run the code.
  • You will get the output. Inside the MsgBox, click OK to end the process.

Output of Delete Alternate Rows in Another Sheet

  • Finally, you can see a before-after scenario of this example below:

Before-After Scenerio of Delete Alternate Rows in Another Sheet


5. Delete Blank Rows in Another Sheet

In this example, the code deletes all blank rows in a specified range of a specified sheet and notifies the user of the deleted rows. If no blank rows are found, it notifies the user that none were found.

Steps:

  • First, we need to insert a module as stated earlier.
  • Then, we need to write the following code inside the module.

Code Image of Delete Blank Rows in Another Sheet

 

Code Syntax:

Sub DeleteBlankRowsInOtherSheet()
Dim sheetName As String
Dim targetSheet As Worksheet
Dim rng As Range, cell As Range, deleteRange As Range
'Get the sheet name from user input
sheetName = InputBox("Enter the name of the sheet to delete blank rows from:")
If sheetName = "" Then
MsgBox "Please enter a sheet name"
Exit Sub
End If
'Attempt to set the target sheet to the sheet with the given name
On Error GoTo ErrorHandler
Set targetSheet = ThisWorkbook.Worksheets(sheetName)
'Get the range to search for blank rows
Set rng = targetSheet.Range("B4:F16")
'Loop through each cell in the range and mark the rows that are blank
For Each cell In rng
If Application.WorksheetFunction.CountA(cell.EntireRow) = 0 Then
If deleteRange Is Nothing Then
Set deleteRange = cell.EntireRow
Else
Set deleteRange = Union(deleteRange, cell.EntireRow)
End If
End If
Next cell
'Delete the marked rows
If Not deleteRange Is Nothing Then
deleteRange.Delete
MsgBox "Blank rows deleted from the sheet: " & sheetName
Else
MsgBox "No blank rows found in the selected range"
End If
Exit Sub
ErrorHandler:
MsgBox "Please enter a valid sheet name."
End Sub

Code Breakdown:

Sub DeleteBlankRowsInOtherSheet()

Subroutine declaration.

Dim sheetName As String

Declare a variable to store the name of the sheet to delete blank rows from.

Dim targetSheet As Worksheet

This line declare a variable to store the worksheet object of the sheet to delete blank rows from.

Dim rng As Range, cell As Range, deleteRange As Range

Declare three variables – rng to store the range to search for blank rows, cell to iterate through each cell in the range, and deleteRange to store the rows to delete.

sheetName = InputBox("Enter the name of the sheet to delete blank rows from:")

Prompt the user to enter the name of the sheet to delete blank rows from.

If sheetName = "" Then

Check if the user has entered a sheet name.

MsgBox "Please enter a sheet name"

Display an error message if the user has not entered a sheet name.

Exit Sub

Exit the subroutine.

On Error GoTo ErrorHandler

If there is an error, jump to the ErrorHandler label.

Set targetSheet = ThisWorkbook.Worksheets(sheetName)

Set targetSheet to the worksheet object of the sheet with the given name.

Set rng = targetSheet.Range("B4:F16")

This line set rng to the range B4:F16 on targetSheet.

For Each cell In rng

Loop through each cell in the range.

If Application.WorksheetFunction.CountA(cell.EntireRow) = 0 Then

Check if the entire row of the cell is empty.

If deleteRange Is Nothing Then

Now, check if deleteRange is empty.

Set deleteRange = cell.EntireRow

If deleteRange is empty, set it to the current row.

Else

Otherwise, if deleteRange is not empty, add the current row to deleteRange.

Set deleteRange = Union(deleteRange, cell.EntireRow)

Add the current row to deleteRange.

End If

End the if statement.

Next cell

Move to the next cell in the range.

If Not deleteRange Is Nothing Then

Check if there are any rows to delete.

deleteRange.Delete

Delete the rows in deleteRange.

MsgBox "Blank rows deleted from the sheet: " & sheetName

Display a message box indicating the rows that were deleted.

Else

If there are no rows to delete.

MsgBox "No blank rows found in the selected range"

Display a message box indicating that no blank rows were found.

End If

End the if statement.

Exit Sub

Exit the subroutine.

ErrorHandler

Label to handle errors.

MsgBox "Please enter a valid sheet name."

Display an error message if the user has entered an invalid sheet name.

End Sub

Ends the definition of the subroutine.

  • Consequently, we need to run the code.
  • You will get the output. Inside the MsgBox, click OK to end the process.

Output of Delete Blank Rows in Another Sheet

  • Finally, you can see a before-after scenario of this example below:

Before-After Scenario of Delete Blank Rows in Another Sheet


6. Delete Row from Multiple Worksheets

In this example, the code prompts the user to input a row number and then deletes the specified row in all sheets of the workbook. It also includes error handling to prompt the user to enter a row number if they do not input one.

Steps:

  • We need to insert a module as stated earlier.
  • Then, we need to write the following code inside the module.

Code Image of Delete Row from Multiple Worksheets

Code Syntax:

Sub DeleteSameRowInAllSheets()
Dim rowNum As Long
Dim ws As Worksheet'Get the row number to be deleted from user input
On Error Resume Next
rowNum = InputBox("Enter the row number to delete:")
If rowNum = "" Then
MsgBox "Please enter a row number"
Exit Sub
End If'Loop through all worksheets in the workbook
For Each ws In ThisWorkbook.Worksheets
'Delete the row in the current worksheet
ws.Rows(rowNum).Delete
Next ws
MsgBox "Row no " & rowNum & " is deleted from all sheets in the workbook."
End Sub

Code Breakdown:

Sub DeleteSameRowInAllSheets()

Defines a new Subroutine called “DeleteSameRowInAllSheets”.

Dim rowNum As Long

Declares a variable called “rowNum” as a Long.

Dim ws As Worksheet

Declares a variable called “ws” as a Worksheet.

On Error Resume Next

This line enables the program to continue executing if an error occurs during the user input of the row number.

rowNum = InputBox("Enter the row number to delete:")

Prompts the user to enter the row number to be deleted and stores the value in the “rowNum” variable.

If rowNum = "" Then

This line checks if the user has entered a value for the row number. If not, a message box will be displayed asking the user to enter a row number and the subroutine will exit.

For Each ws In ThisWorkbook.Worksheets

Starts a loop that goes through each worksheet in the workbook and sets the “ws” variable to the current worksheet.

ws.Rows(rowNum).Delete

Deletes the row with the specified row number in the current worksheet (“ws”).

Next ws

Moves to the next worksheet in the loop.

MsgBox "Row no " & rowNum & " is deleted from all sheets in the workbook."

Displays a message box that confirms the row has been deleted from all worksheets in the workbook.

End Sub

Ends the definition of the subroutine.

  • Consequently, we need to run the code. And the desired row number will be deleted from all the worksheets in the workbook.
  • Finally, you will get the output. Inside the MsgBox, click OK to end the process.

Output of Delete Row from Multiple Worksheets


7. Delete Row in Another Sheet Based on Empty/Blank Cells

In this example, the code prompts the user to enter the name of a worksheet and a range in that sheet to search for blank cells. If any blank cells are found, the entire row containing the blank cells is deleted. A message is displayed to the user indicating which rows were deleted. If the sheet name entered by the user is invalid, an error message is displayed.

Steps:

  • First, we need to insert a module as stated earlier.
  • Then, we need to write the following code inside the module.

Code Image of Delete Row in Another Sheet Based on Empty/Blank Cells

Code Syntax:

Sub DeleteRowInOtherSheet()
Dim sheetName As String
Dim targetSheet As Worksheet
Dim rng As Range, cell As Range'Get the sheet name from user input
sheetName = InputBox("Enter the name of the sheet to delete rows from:")
If sheetName = "" Then
MsgBox "Please enter a sheet name"
Exit Sub
End If'Attempt to set the target sheet to the sheet with the given name
On Error GoTo ErrorHandler
Set targetSheet = ThisWorkbook.Worksheets(sheetName)'Set the range to search for blank cells
Set rng = targetSheet.Range("B4:F14")'Loop through each cell in the range and delete the row if a blank cell is found
For Each cell In rng
If cell.Value = "" Then
cell.EntireRow.Delete
End If
Next cell'Display message to user
MsgBox "Rows containing one or more blank/empty cells have been deleted from the sheet: " & sheetName
Exit Sub
ErrorHandler:
MsgBox "Please enter a valid sheet name."
End Sub

Code Breakdown:

Sub DeleteRowInOtherSheet()

– This line is the start of a VBA subroutine named “DeleteRowInOtherSheet”.

Dim sheetName As String

– Declares a variable named “sheetName” as a string.

Dim targetSheet As Worksheet

– This line, declares a variable named “targetSheet” as a worksheet.

Dim rng As Range, cell As Range

– Declares two variables named “rng” and “cell” as ranges.

'Get the sheet name from user input

 – A comment indicating the following line of code will prompt the user to input a sheet name.

sheetName = InputBox("Enter the name of the sheet to delete rows from:")

– Prompts the user to input a sheet name and assigns it to the “sheetName” variable.

If sheetName = "" Then

 – Checks if the “sheetName” variable is an empty string.

MsgBox "Please enter a sheet name"

– Displays a message box asking the user to enter a sheet name if “sheetName” variable is empty.

Exit Sub

– Exits the subroutine if “sheetName” variable is empty.

'Attempt to set the target sheet to the sheet with the given name

– A comment indicating the following line of code will attempt to assign the “targetSheet” variable to the sheet with the given name.

On Error GoTo ErrorHandler

 – Enables error handling and directs the code to jump to the “ErrorHandler” label if an error occurs.

Set targetSheet = ThisWorkbook.Worksheets(sheetName)

– Assigns the worksheet with the name specified in “sheetName” variable to the “targetSheet” variable.

'Set the range to search for blank cells

– A comment indicating the following line of code will assign the “rng” variable to a range of cells to search for blank cells.

Set rng = targetSheet.Range("B4:F14")

– Assigns the range of cells “B4:F14” from the “targetSheet” worksheet to the “rng” variable.

'Loop through each cell in the range and delete the row if a blank cell is found

– A comment indicating the following code block will loop through each cell in the “rng” range and delete the entire row if a blank cell is found.

For Each cell In rng

– Loops through each cell in the “rng” range and assigns it to the “cell” variable.

If cell.Value = "" Then

– Checks if the value of the current “cell” variable is an empty string.

cell.EntireRow.Delete

– Deletes the entire row of the current “cell” variable if it is blank.

Next cell

– Goes to the next cell in the “rng” range to be checked.

'Display message to user

– A comment indicating the following line of code will display a message box to the user.

MsgBox "Rows containing one or more blank/empty cells have been deleted from the sheet: " & sheetName

– Displays a message box to the user indicating the rows containing one or more blank/empty cells have been deleted from the specified sheet.

Exit Sub

– Exits the subroutine.

'ErrorHandler label

– A comment indicating that the following line of code defines the ErrorHandler” label.

MsgBox "Please enter a valid sheet name."

– Displays a message box to the user indicating that a valid sheet name should be entered.

End Sub

– Ends the definition of the subroutine.

  • Run the code.
  • You will get the output. Inside the MsgBox, click OK to end the process.

Output of of Delete Row in Another Sheet Based on Empty/Blank Cells

  • Finally, you can see a before-after scenario of this example below:

Before-After Scenario of Delete Row in Another Sheet Based on Empty/Blank Cells


8. Delete Duplicate Rows in Another Sheet

In this example, this code deletes duplicate rows in a specified range on a worksheet, based on the values in the cells of columns B to F. The user is prompted to input the name of the worksheet to perform the operation on. If duplicate rows are found, they are deleted, and a message is displayed to indicate that the operation was successful. If the user enters an invalid worksheet name, an error message is displayed.

Steps:

  • First, we need to insert a module as stated earlier.
  • Then, we need to write the following code inside the module.

Code Image of Delete Duplicate Rows in Another Sheet

Code Syntax:

Sub DeleteDuplicateRowsInOtherSheet()
Dim targetSheet As Worksheet
Dim rng As Range
'Get the sheet name to delete duplicate rows from
sheetName = InputBox("Enter the name of the sheet to delete duplicate rows from:")
If sheetName = "" Then
MsgBox "Please enter a sheet name"
Exit Sub
End If
'Attempt to set the target sheet to the sheet with the given name
On Error GoTo ErrorHandler
Set targetSheet = ThisWorkbook.Worksheets(sheetName)
'Define the range to check for duplicate rows
Set rng = targetSheet.Range("B4:F14")
'Get the last row in the range
lastRow = rng.Rows.Count + rng.Row - 1
'Loop through each row in the range and check for duplicates
For i = rng.Row To lastRow - 1
For j = i + 1 To lastRow
If targetSheet.Cells(i, 1).Value = targetSheet.Cells(j, 1).Value _
And targetSheet.Cells(i, 2).Value = targetSheet.Cells(j, 2).Value _
And targetSheet.Cells(i, 3).Value = targetSheet.Cells(j, 3).Value _
And targetSheet.Cells(i, 4).Value = targetSheet.Cells(j, 4).Value _
And targetSheet.Cells(i, 5).Value = targetSheet.Cells(j, 5).Value Then
targetSheet.Rows(j).Delete
lastRow = lastRow - 1
j = j - 1
End If
Next j
Next i
MsgBox "Duplicate rows have been deleted from the sheet: " & sheetName
Exit Sub
ErrorHandler:
MsgBox "Please enter a valid sheet name."
End Sub

Code Breakdown:

Sub DeleteDuplicateRowsInOtherSheet()

– declares a subroutine with a name ‘DeleteDuplicateRowsInOtherSheet’.

Dim targetSheet As Worksheet

– This line declares a variable ‘targetSheet’ as Worksheet data type.

Dim rng As Range

– declares a variable ‘rng’ as Range data type.

sheetName = InputBox("Enter the name of the sheet to delete duplicate rows from:")

– displays an input box to get the sheet name from the user.

If sheetName = "" Then

– checks if the user input is empty.

MsgBox "Please enter a sheet name"

– displays an error message if the user input is empty.

Exit Sub

– exits the subroutine.

On Error GoTo ErrorHandler

– enables error handling and directs the code to the ErrorHandler label if an error occurs.

Set targetSheet = ThisWorkbook.Worksheets(sheetName)

– assigns the worksheet with the user-inputted sheet name to the targetSheet variable.

Set rng = targetSheet.Range("B4:F14")

– This line assigns the range B4:F14 in the targetSheet to the rng variable.

lastRow = rng.Rows.Count + rng.Row - 1

– gets the last row number in the rng range.

For i = rng.Row To lastRow - 1

– loops through each row in the rng range.

For j = i + 1 To lastRow

– Here, this line loops through each row in the rng range starting from the next row after the i-th row.

If targetSheet.Cells(i, 1).Value = targetSheet.Cells(j, 1).Value _

– checks if the cell values in each column for row i and row j are equal.

targetSheet.Rows(j).Delete

– deletes the row j if it is a duplicate row.

lastRow = lastRow - 1

– updates the lastRow variable after a row is deleted.

j = j - 1

– decrements the j variable to ensure the loop doesn’t skip a row after a deletion.

MsgBox "Duplicate rows have been deleted from the sheet: " & sheetName

– displays a message box to inform the user that duplicate rows have been deleted from the sheet.

Exit Sub

– exits the subroutine.

ErrorHandler

– specifies a label to direct the code to in case an error occurs.

MsgBox "Please enter a valid sheet name."

– displays an error message to inform the user that the sheet name entered is not valid.

End Sub

– Ends the definition of the subroutine.

  • Consequently, we need to run the code.
  • You will get the output. Inside the MsgBox, click OK to end the process. Thus, you can delete row on another sheet using Excel VBA.

Output of Delete Duplicate Rows in Another Sheet

  • Finally, you can see a before-after scenario of this example below:

Before-After Scenerio of Delete Duplicate Rows in Another Sheet


9.Delete Last Row in Another Sheet

In this example, the code asks the user to enter a sheet name, then attempts to set the target sheet to that sheet. It then finds the last row of a range in that sheet and deletes it. A message box is displayed to the user indicating which row has been deleted from which sheet. If the sheet name entered is invalid, an error message is displayed.

Steps:

  • First, we need to insert a module as stated earlier.
  • Then, we need to write the following code inside the module.

Code Image of Delete Last Row in Another Sheet

Code Syntax:

Sub DeleteLastRowInOtherSheet()
Dim sheetName As String
Dim lastRowNum As Long
Dim targetSheet As Worksheet
Dim rng As Range'Get the sheet name from user input
sheetName = InputBox("Enter the name of the sheet to delete the last row from:")
If sheetName = "" Then
MsgBox "Please enter a sheet name"
Exit Sub
End If'Attempt to set the target sheet to the sheet with the given name
On Error GoTo ErrorHandler
Set targetSheet = ThisWorkbook.Worksheets(sheetName)'Set the range to search for the last row
Set rng = targetSheet.Range("B4:F14")'Find the last row within the range
lastRowNum = rng.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row'Delete the last row in the target sheet
targetSheet.Rows(lastRowNum).DeleteMsgBox "Last row (row no " & lastRowNum & ") is deleted from the sheet: " & sheetName
Exit Sub
ErrorHandler:
MsgBox "Please enter a valid sheet name."
End Sub

Code Breakdown:

Sub DeleteLastRowInOtherSheet()

– Start of the subroutine.

Dim sheetName As String

– Declares a variable named sheetName as a string data type.

Dim lastRowNum As Long

– This line declares a variable named lastRowNum as a long data type.

Dim targetSheet As Worksheet

– Declares a variable named targetSheet as a Worksheet object.

Dim rng As Range

– Here, this line declares a variable named rng as a Range object.

'Get the sheet name from user input

– A comment describing the next lines of code.

sheetName = InputBox("Enter the name of the sheet to delete the last row from:")

– Prompts the user to enter the name of the sheet to delete the last row from and store the input in the sheetName variable.

If sheetName = "" Then

– Checks if sheetName variable is empty.

MsgBox "Please enter a sheet name"

– Displays a message if sheetName variable is empty.

Exit Sub

– Terminates the subroutine if sheetName variable is empty.

On Error GoTo ErrorHandler

– Starts the error handler.

Set targetSheet = ThisWorkbook.Worksheets(sheetName)

– Sets the targetSheet variable to the worksheet with the name specified in the sheetName variable.

Set rng = targetSheet.Range("B4:F14")

– Sets the range to search for the last row in the target sheet.

lastRowNum = rng.Find("*", SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row

– Finds the last row within the range and store the row number in the lastRowNum variable.

targetSheet.Rows(lastRowNum).Delete

– Deletes the last row in the target sheet.

'MsgBox "Last row (row no " & lastRowNum & ") is deleted from the sheet: " & sheetName"

 – Displays a message stating that the last row has been deleted from the specified sheet.

Exit Sub

– Terminates the subroutine.

ErrorHandler

– Labels for the error handler.

MsgBox "Please enter a valid sheet name."

– Displays an error message if an invalid sheet name is entered.

End Sub

– Ends the definition of the subroutine.

  • Consequently, we need to run the code.
  • After getting the output, click OK to end the process inside the MsgBox. Thus, you can delete row on another sheet using Excel VBA.

Output of Delete Last Row in Another Sheet

  • Finally, you can see a before-after scenario of this example below:

Before-After Scenerio of Delete Last Row in Another Sheet


10. Delete Only Contents of a Row But Keep Formatting

In this example, the code prompts the user to input a sheet name and row number, then clears the contents of the specified row in the specified sheet while preserving formatting. It displays a message box indicating that the contents have been cleared. If an invalid sheet name is entered, it displays an error message.

Steps:

  • First, we need to insert a module as stated earlier.
  • Then, we need to write the following code inside the module.

Code Image of Delete Only Contents of a Row But Keep Formatting

Code Syntax:

Sub ClearRowContentsInOtherSheet()
Dim sheetName As String
Dim rowNum As Long
Dim targetSheet As Worksheet'Get the sheet name and row number to clear contents from user input
sheetName = InputBox("Enter the name of the sheet to clear contents from:")
If sheetName = "" Then
MsgBox "Please enter a sheet name"
Exit Sub
End If
'Attempt to set the target sheet to the sheet with the given name
On Error GoTo ErrorHandler
Set targetSheet = ThisWorkbook.Worksheets(sheetName)
rowNum = InputBox("Enter the row number to clear contents:")
'Clear contents of the row in the target sheet
targetSheet.Rows(rowNum).ClearContents
MsgBox "Contents of row no " & rowNum & " is cleared from the sheet: " & sheetName & ". But the formatting is intact."
Exit Sub
ErrorHandler:
MsgBox "Please enter a valid sheet name."
End Sub

Code Breakdown:

Sub ClearRowContentsInOtherSheet()

This is the start of the VBA subroutine.

Dim sheetName As String

Declares a variable to store the name of the sheet to clear contents from.

Dim rowNum As Long

Next, this line declares a variable to store the row number to clear contents from.

Dim targetSheet As Worksheet

Declares a variable to store the target worksheet to clear contents from.

sheetName = InputBox("Enter the name of the sheet to clear contents from:")

Asks the user to input the name of the sheet to clear contents from and stores it in the ‘sheetName’ variable.

If sheetName = "" Then

Checks if the ‘sheetName’ variable is empty.

MsgBox "Please enter a sheet name"

If the ‘sheetName’ variable is empty, displays a message box to remind the user to enter a valid sheet name.

Exit Sub

Exits the subroutine if the ‘sheetName’ variable is empty.

On Error GoTo ErrorHandler

Sets an error handler to jump to the ‘ErrorHandler’ label if an error occurs.

Set targetSheet = ThisWorkbook.Worksheets(sheetName)

This line sets the ‘targetSheet’ variable to the worksheet with the name stored in the ‘sheetName’ variable.

rowNum = InputBox("Enter the row number to clear contents:")

Asks the user to input the row number to clear contents from and stores it in the ‘rowNum’ variable.

targetSheet.Rows(rowNum).ClearContents

Clears the contents of the row specified by the ‘rowNum’ variable in the ‘targetSheet’.

MsgBox "Contents of row no " & rowNum & " is cleared from the sheet: " & sheetName & ". But the formatting is intact."

Displays a message box to inform the user that the contents of the specified row in the specified sheet have been cleared, while the formatting remains intact.

Exit Sub

Exits the subroutine.

ErrorHandler

Handles any errors that occur during the execution of the subroutine. If an error occurs, displays a message box to remind the user to enter a valid sheet name.

End Sub

Ends the definition of the subroutine.

  • Run the code.
  • You will get the output. Inside the MsgBox, click OK to end the process. Thus, you can delete row on another sheet using Excel VBA.

Output of Delete Only Contents of a Row But Keep Formatting

  • Finally, you can see a before-after scenario of this example below:

Before-After Scenerio of Delete Only Contents of a Row But Keep Formatting

Read More: Excel VBA to Delete Table Row


How to Delete Row in Another Workbook with Excel VBA?

Until now, you have seen the techniques of deleting a row in a sheet while staying on a different sheet inside the same workbook under various conditions. Now is the time for you to learn how to delete a row in another workbook, which you have not even opened. This code deletes a specific row in a specified sheet of another workbook. It prompts the users to input the name and path of the target workbook, as well as the sheet name and row number they want to delete. It then attempts to open the target workbook and delete the specified row in the specified sheet. If an error occurs, it displays an error message and closes the target workbook.

Steps:

  • We need to insert a module as stated earlier.
  • After that, we need to write the following code inside the module.

Code Image of How to Delete Row in Another Workbook with Excel VBA

Code Syntax:

Sub DeleteRowInOtherWorkbook()
Dim wbName As String
Dim wbPath As String
Dim wbFullName As String
Dim rowNum As Long
Dim targetSheet As Worksheet
Dim targetWorkbook As Workbook
' Get the name and path of the target workbook from the user
wbName = Application.InputBox("Enter the name of the target workbook:", Type:=2)
If wbName = "False" Then
MsgBox "Please enter a valid workbook name."
Exit Sub
End If
wbPath = Application.InputBox("Enter the full path of the target workbook:", Type:=2)
If wbPath = "False" Then
MsgBox "Please enter a valid workbook path."
Exit Sub
End If
wbFullName = wbPath & "\" & wbName
' Attempt to open the target workbook
On Error GoTo ErrorHandler
Set targetWorkbook = Workbooks.Open(wbFullName)
' Get the sheet name and row number to be deleted from user input
sheetName = InputBox("Enter the name of the sheet to delete rows from:")
If sheetName = "" Then
MsgBox "Please enter a sheet name"
targetWorkbook.Close False
Exit Sub
End If
rowNum = InputBox("Enter the row number to delete:")
' Attempt to delete the row in the target sheet
Set targetSheet = targetWorkbook.Worksheets(sheetName)
On Error GoTo ErrorHandler
targetSheet.Rows(rowNum).Delete
MsgBox "Row no " & rowNum & " is deleted from the sheet: " & sheetName & " in workbook " & wbName
targetWorkbook.Close SaveChanges:=True
Exit SubErrorHandler:
MsgBox "An error occurred while trying to access the target workbook."
If Not targetWorkbook Is Nothing Then targetWorkbook.Close False
End Sub

Code Breakdown:

Sub DeleteRowInOtherWorkbook()

This line declares the name of the sub-procedure.

Dim wbName As String

Variable wbName is a string that holds the name of the target workbook.

Dim wbPath As String

This line declares a variable wbPath as a string that holds the full path of the target workbook.

Dim wbFullName As String

Declares a variable wbFullName as a string that holds the full name (path + name) of the target workbook.

Dim rowNum As Long

This line declares a variable rowNum as a long integer that holds the row number that user want to delete.

Dim targetSheet As Worksheet

Now, this line declares a variable targetSheet as a worksheet object that holds the sheet in the target workbook where user wants to delete the row.

Dim targetWorkbook As Workbook

This line declares a variable targetWorkbook as a workbook object that holds the target workbook.

wbName = Application.InputBox("Enter the name of the target workbook:", Type:=2)

Prompts the user to enter the name of the target workbook and assigns the input value to the wbName variable.

If wbName = "False" Then

This line checks if the user has entered a valid workbook name.

MsgBox "Please enter a valid workbook name."

Displays a message box to the user to enter a valid workbook name.

Exit Sub

Terminates the sub-procedure.

wbPath = Application.InputBox("Enter the full path of the target workbook:", Type:=2)

Prompts the user to enter the full path of the target workbook and assigns the input value to the wbPath variable.

If wbPath = "False" Then

Checks if the user has entered a valid workbook path.

MsgBox "Please enter a valid workbook path."

Displays a message box to the user to enter a valid workbook path.

wbFullName = wbPath & "" & wbName

Concatenates the workbook path and name to get the full name of the workbook.

On Error GoTo ErrorHandler

Directs the code to jump to the ErrorHandler label if an error occurs.

Set targetWorkbook = Workbooks.Open(wbFullName)

Opens the target workbook and assigns it to the targetWorkbook variable.

sheetName = InputBox("Enter the name of the sheet to delete rows from:")

Prompts the users to enter the name of the sheet from which they want to delete row and assigns the input value to the sheetName variable.

If sheetName = "" Then

Checks if the user has entered a sheet name.

MsgBox "Please enter a sheet name"

Displays a message box to the user to enter a sheet name.

targetWorkbook.Close False

Closes the target workbook without saving any changes.

Exit Sub

Terminates the sub-procedure.

rowNum = InputBox("Enter the row number to delete:")

Prompts the users to enter the row number which they want to delete and assigns the input value to the rowNum variable.

Set targetSheet = targetWorkbook.Worksheets(sheetName)

Sets the targetSheet variable to the sheet in the target workbook where you want to delete the row.

On Error GoTo ErrorHandler

This line directs the code to jump to the ErrorHandler label if an error occurs.

targetSheet.Rows(rowNum).Delete

Deletes the row from the target sheet.

MsgBox "Row no " & rowNum & " is deleted from the sheet: " & sheetName & " in workbook " & wbName

Displays a message box.

End Sub

Ends the definition of the subroutine.

  • Consequently, we need to run the code.
  • First, we need to type the Workbook name. Press OK. Then, type the workbook path. (you can copy the path from the properties of the workbook as demonstrated inside the video). Press OK. Afterward, type the Sheet name of that workbook from which we want to delete a row. Press OK. Lastly, type the row number we want to delete.
  • You will get the output. Inside the MsgBox, click OK to end the process. Thus, you can delete row on another sheet using Excel VBA.

Output of How to Delete Row in Another Workbook with Excel VBA

  • Finally, you can see a before-after scenario of this example below:

“Before” Scenerio of How to Delete Row in Another Workbook with Excel VBA

“After” Scenario of How to Delete Row in Another Workbook with Excel VBA


How to Delete Rows in a Range with Excel VBA?

In this section, the code deletes multiple rows in the active sheet of the current workbook. The code selects a range of rows that you want to delete and then loops through each row in the range, deleting it from the target sheet. Then, we will confirm that we have deleted desired rows via message.

Steps:

  • First, we need to insert a module as stated earlier.
  • Then, we need to write the following code inside the module.

Code Image of How to Delete Rows in a Range with Excel VBA

Code Syntax:

Sub DeleteMultipleRowsInOtherSheet()
Dim sheetName As String
Dim rowRange As Range
Dim targetSheet As Worksheet'Attempt to set the target sheet to the sheet with the given name
Set targetSheet = ThisWorkbook.ActiveSheetSet rowRange = Range("B4:F6")
'Loop through each row in the range and delete it from the target sheet
Dim i As Long
For i = rowRange.Rows.Count To 1 Step -1
targetSheet.Rows(rowRange.Rows(i).Row).Delete
Next i
MsgBox "Desired Rows are deleted."
Exit Sub
End Sub

Code Breakdown:

Sub DeleteMultipleRowsInOtherSheet()

This line starts the definition of the VBA subroutine called “DeleteMultipleRowsInOtherSheet”.

Dim sheetName As String

Declares a string variable named “sheetName”.

Dim rowRange As Range

This declares a range variable named “rowRange”.

Dim targetSheet As Worksheet

Then this line declares a worksheet variable named “targetSheet”.

Set targetSheet = ThisWorkbook.ActiveSheet

This sets the targetSheet variable to the active sheet in the current workbook.

Set rowRange = Range("B4:F6")

Sets the rowRange variable to the range of cells B4 through F6 in the active sheet.

Dim i As Long

Declares a variable i as a Long integer.

For i = rowRange.Rows.Count To 1 Step -1

This is a For loop that starts at the last row in the rowRange variable and loops through each row until it reaches the first row, deleting each row as it goes.

targetSheet.Rows(rowRange.Rows(i).Row).Delete

Deletes the current row in the loop from the targetSheet.

Next i

End of the For loop.

MsgBox "Desired Rows are deleted."

Displays a message box to the users letting them know that they have deleted desired rows.

Exit Sub

Exits the subroutine.

End Sub

Ends the definition of the subroutine.

  • Consequently, we need to run the code.
  • You will get the output. Inside the MsgBox, click OK to end the process. Thus, you can delete row on another sheet using Excel VBA.

Output of How to Delete Rows in a Range with Excel VBA

  • Finally, you can see a before-after scenario of this example below:

Before-After Scenario of How to Delete Rows in a Range with Excel VBA

Read More: How to Delete Rows in a Range with VBA in Excel


How to Delete Active Row with Excel VBA?

Here, The code prompts the user to confirm whether they want to delete the active row, deletes the active row if confirmed, and then informs the users that they have deleted row.

Steps:

  • First, we need to insert a module as stated earlier.
  • Then, we need to write the following code inside the module.

Code Image of How to Delete Active Row with Excel VBA

Code Syntax:

Sub DeleteActiveRow()
'Prompt user for confirmation before deleting row
Dim response As Variant
response = MsgBox("Are you sure you want to delete the active row?", vbYesNo)
If response = vbNo Then Exit Sub'Delete the active row
Application.ActiveCell.EntireRow.Delete'Inform user that row has been deleted
MsgBox "The active row has been deleted."
End Sub

Code Breakdown:

Sub DeleteActiveRow()

Defines the name of the subroutine that will delete the active row.

Dim response As Variant

Declares a variable called “response” as a Variant data type.

response = MsgBox("Are you sure you want to delete the active row?", vbYesNo)

Displays a message box with a yes/no option and assigns the user’s response to the “response” variable.

If response = vbNo Then Exit Sub

Checks if the user responded “No” and exits the subroutine if they did.

Application.ActiveCell.EntireRow.Delete

eletes the entire row that contains the active cell.

MsgBox "The active row has been deleted."

This line displays a message box to inform the user that the you have deleted active row.

End Sub

This line ends the definition of the subroutine.

  • Consequently, we need to run the code.
  • You will get the output. Inside the MsgBox, click OK to end the process. Thus, you can delete row on another sheet using Excel VBA.

Output of How to Delete Active Row with Excel VBA

  • Finally, you can see a before-after scenario of this example below:

Before-After Scenerio of How to Delete Active Row with Excel VBA


Things to Remember

  • Be careful when deleting rows as you cannot undo this operation. It is always a good practice to create a backup of your data before performing any delete operation.
  • When deleting rows, ensure that you select the correct row range. Check the start and end row numbers before deleting to avoid accidental deletion of important data.
  • If you are deleting rows based on certain criteria, ensure that your code is robust enough to handle different scenarios. Test your code thoroughly and consider all possible edge cases.
  • Always provide appropriate error handling in your VBA code. This will help you avoid runtime errors and provide useful feedback to the user if something goes wrong.

Download Practice Workbook

You can download this workbook.


Conclusion

In conclusion, using Excel VBA to delete rows from another sheet in Excel is an effective way for managing large amounts of data efficiently. With just a few lines of code, you can do repetitive tasks automatically, saving time and effort. However, it is important to use caution when deleting data, as mistakes can result in the loss of important information. You need proper testing and verification of code to ensure that you have deleted the intended rows. Overall, mastering this skill can greatly enhance productivity and streamline data management tasks in Excel.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Nafis Soumik
Md. Nafis Soumik

Md. Nafis Soumik graduated from Bangladesh University of Engineering & Technology, Dhaka, with a BSc.Engg in Naval Architecture & Marine Engineering. In January 2023, he joined Softeko as an Excel and VBA content developer, contributing 50+ articles on topics including Data Analysis, Visualization, Pivot Tables, Power Query, and VBA. Soumik participated in 2 specialized training programs on VBA and Chart & Dashboard designing in Excel. During leisure, he enjoys music, travel, and science documentaries, reflecting a diverse range... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo