Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to Delete Same Rows or Ranges across Multiple Sheets in Excel
Microsoft Excel is a robust spreadsheet programme that is widely used for data management, analysis, and reporting. When working with huge datasets spread across numerous sheets, deleting the same rows or ranges of data in each sheet separately can become time-consuming and laborious. Fortunately, Excel includes a number of tools and approaches that can help you expedite this process and save you time and effort.
Delete Same Rows or Ranges Across Multiple Sheets
Here, we will first create a VBA module and then run it to complete the task. So let us see a simple process to learn how you can delete the same rows or ranges across multiple sheets in Excel.
Step 1
Consider an Excel book where you have data on multiple sheets in the same range.
First, right-click on any sheet name and select "View Code" to open the VBA application.
Right click > View code.
Step 2
Then click on Insert, select Module, and copy the below code into the text box.
Insert > Module.
Example
Sub bleh()
Dim xWs As Worksheet
Set xWs = ActiveSheet
ThisWorkbook.Worksheets.Select
Rows("4:5").Select
Selection.Delete
xWs.Select
End Sub
In the code 4:5 will represent to delete the 4 and 5 rows.
Step 3
Then click F5 to run the module, and you can see that data in the specified range will be deleted from all the sheets.
Note: If you want to delete cells by selecting a range, use the code given below.
Example
Private Sub CommandButton2_Click()
Dim xRg As Range
Dim xTxt As String
Dim xWs As Worksheet
On Error Resume Next
Set xWs = ActiveSheet
If ActiveWindow.RangeSelection.Count > 1 Then
xTxt = ActiveWindow.RangeSelection.AddressLocal
Else
xTxt = ActiveSheet.UsedRange.AddressLocal
End If
Set xRg = Application.InputBox("Please select the range you want to delete across multiple sheets:", "Delete Same Rows", xTxt, , , , , 8)
If xRg Is Nothing Then Exit Sub
xRg.ClearContents
ThisWorkbook.Worksheets.Select
ActiveWindow.SelectedSheets.FillAcrossSheets xRg, xlFillWithContents
xWs.Select
End Sub
Conclusion
In this tutorial, we have used a simple example to demonstrate how you can delete the same rows or ranges across multiple sheets in Excel to highlight a particular set of data.
120 Views
