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 Rows with Negative Values in Excel
Negative values can occasionally interfere with data analysis or calculations, and deleting them might help you focus on the good parts of your data. Excel provides effective options for filtering out undesired rows with negative values, whether you're working with financial records, survey results, or any other type of data. In this tutorial, we will walk you through the process of detecting and eliminating rows in your Excel spreadsheet that have negative values.
Deleting Excel Rows with Negative Values
Here we will first create a VBA module and select the range of cells to complete the task. So let us see a simple process to learn how you can delete rows with negative values in Excel.
Step 1 : Consider an Excel sheet where you have rows with negative values, similar to the one shown below:
First, right−click on the 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 > Copy.
Example
Sub Deleter()
Dim xRg As Range
Dim xCell As Range
Dim xTxt As String
Dim I As Long
On Error Resume Next
xTxt = ActiveWindow.RangeSelection.Address
Sel:
Set xRg = Nothing
Set xRg = Application.InputBox("please select the data range:", "Delete Rows Negative", xTxt, , , , , 8)
If xRg Is Nothing Then Exit Sub
If xRg.Areas.Count > 1 Then
MsgBox "does not support multiple selections, please select again", vbInformation, "Delete Rows Negative"
GoTo Sel
End If
If xRg.Columns.Count > 1 Then
MsgBox "does not support multiple columns, please select again", vbInformation, "Delete Rows Negativel"
GoTo Sel
End If
For I = xRg.Rows.Count To 1 Step -1
If xRg.Cells(I) < 0 Then xRg.Cells(I).EntireRow.Delete
Next
End Sub
Step 3 :Then click F5 to run the code, select the range of cells containing the negative values, and click Ok. Then you can see that rows with negative values will be deleted.
F5 > Select cells > Ok.
Conclusion
In this tutorial, we have used a simple example to demonstrate how you can delete rows with negative values in Excel to highlight a particular set of data.
568 Views
