• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Deleting rows if certain cells in the row are 0|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / Deleting rows if certain cells in the row are 0|VBA & Macros|Excel Forum|My Online Training Hub

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosDeleting rows if certain cells in t…
sp_PrintTopic sp_TopicIcon
Deleting rows if certain cells in the row are 0
Avatar
cbaker

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
1
April 27, 2017 - 5:11 am
sp_Permalink sp_Print

Hi:

I have a worksheet where I need to remove all rows if certain cells have a value of zero.  Is there a code that will do this easily?

Thanks in advance.

C.Baker

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1431
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
2
April 27, 2017 - 9:13 am
sp_Permalink sp_Print

Hi Baker

Without seeing some sample data, it is difficult to determine where the zero values are. If they are all in a single column then you can give this code a try.

It checks for zero values along column A and delete the entire row if found.

Sub DeleteRow()
Dim r As Long
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For r = LastRow To 1 Step -1
If Cells(r, 1) = 0 Then
Rows(r).Delete
End If
Next r
End Sub

Sunny

Avatar
cbaker

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
3
April 28, 2017 - 6:28 am
sp_Permalink sp_Print

Sunny:

Thank you for taking the time to answer my question.  You stated the code will check for zero values in column A then delete if found.

What about if the cell I need to reference is not in column A.  Actually I have two different columns that I need to reference to see if cell(s) have a zero value but are on the same row.  

Attached is a file that shows what I need.  It has several hidden columns but I have highlighted (yellow) the 2 columns that need to be checked for the zero value.  The first column may have a value in a cell but not in the second column.  I would not want that row deleted.  Only rows that have a zero value in both columns should be deleted.

I may be asking for something that is too complicated.  But I figured I would give it a shotConfused

Thanks

CBaker

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1431
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
4
April 28, 2017 - 9:38 am
sp_Permalink sp_Print sp_EditHistory

Hi Cindy

Your data is a bit more complicated (and dangerous) to delete due to the blank rows on top and hidden columns that also contains data.

As a safety measure, the code below will add text to column BI to see if that row can be deleted. Check to ensure that it is correct.

Once you are sure that the code is OK then you can remove the line in blue and use Rows(r).Delete instead.

Sub DeleteRow()
Dim r As Long
Dim FirstRow As Long
Dim LastRow As Long

'Need to avoid the top blank rows and grand total
FirstRow = 7
LastRow = Cells(Rows.Count, "BG").End(xlUp).Row - 1
For r = LastRow To FirstRow Step -1
If Cells(r, "BD") = 0 And Cells(r, "BG") = 0 Then
Cells(r, "BI") = "Delete" 'remove if code OK
'Rows(r).Delete 'use this if code OK
End If
Next r
End Sub

Hope this helps

Sunny

Avatar
cbaker

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
5
May 3, 2017 - 4:39 am
sp_Permalink sp_Print

Hi Sunny:

I am just getting around to trying the code.  It worked perfectly!!  Thank you so much!

CBaker

Avatar
Kasonde Bwalya

New Member
Members
Level 0
Forum Posts: 1
Member Since:
October 23, 2017
sp_UserOfflineSmall Offline
6
July 8, 2019 - 9:22 pm
sp_Permalink sp_Print

Hi I would like to automatically delete the rows with zeros (the bottom 3)in the data table like below...

X Y Z HOLE ID PLANNED TIMING EDD ID
4868.1 10551.97 1335.39 O40 1439 #N/A
4871.41 10548.92 1335.54 O41 1492 #N/A
4874.75 10545.91 1335.65 O42 1545 #N/A
0 0 0 0 0 9361
0 0 0 0 0 9361
0 0 0 0 0 9361

This data is generated by a macro. Any advise please! 

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1431
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
7
July 10, 2019 - 11:27 am
sp_Permalink sp_Print

Hi Kasonde

If your data starts from A1, then the codes from my post #2 above should work for you.

Good luck.

Sunny

Avatar
Sean Hayes
Member
Members
Level 0
Forum Posts: 15
Member Since:
January 17, 2020
sp_UserOfflineSmall Offline
8
January 17, 2020 - 6:36 am
sp_Permalink sp_Print

Hi Sunny,

Your code in post #2 works brilliantly for deleting the row on the active sheet.

What I am requiring is very similiar to this code.

I have a workbook with 5 worksheets in it. I need the code to search the data in worksheet 'Inventory List' column E.

Where there is a zero I need that row deleting. 

Here comes the bit I cannot figure out.

In worksheets 'Inventory Booked In' and 'Inventory Booked Out' I need the same corresponding data deleting.

In all 3 worksheets Column A contains the same data i.e. A unique code for every entry

Only in 'Inventory List' is the column that contains a quantity.

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1431
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
9
January 17, 2020 - 1:02 pm
sp_Permalink sp_Print

Hi Sean

Just change the column letter in RED to the column (column E in this example) that you want to check.

Sub DeleteRow()
Dim r As Long
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For r = LastRow To 1 Step -1
If Cells(r, "E") = 0 Then
Rows(r).Delete
End If
Next r
End Sub

Hope this helps

Sunny

Avatar
Sean Hayes
Member
Members
Level 0
Forum Posts: 15
Member Since:
January 17, 2020
sp_UserOfflineSmall Offline
10
January 17, 2020 - 4:46 pm
sp_Permalink sp_Print

Hi Sunny,

 

Thanks for your reply.

I knew I had to change If Cells(r, "E") = 0 Then

 

The bit I cannot figure out is.

In worksheets 'Inventory Booked In' and 'Inventory Booked Out' I need the same corresponding data deleting.

In all 3 worksheets Column A contains the same data i.e. A unique code for every entry

Only in 'Inventory List' is the column that contains a quantity.

I.E. If E5 in 'Inventory List' is a zero the above code would delete row 5

What I need next is the data that is in A5 to be searched for in 'Inventory In' & 'Inventory Out' and then delete the corresponding row

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1431
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
11
January 18, 2020 - 11:19 am
sp_Permalink sp_Print

Give this a try

Sub DeleteRow()
Dim r As Long
Dim CodeNo
Dim LastRow As Long
Dim RowNo
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet

Set ws1 = Worksheets("Inventory list")
Set ws2 = Worksheets("Inventory Booked In")
Set ws3 = Worksheets("Inventory Booked Out")

With ws1
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
For r = LastRow To 1 Step -1
If .Cells(r, "E") = 0 Then

'Get the unique code number then delete the required rows
CodeNo = .Cells(r, 1)
.Rows(r).Delete

'Search for code and delete row if found
RowNo = Application.Match(CodeNo, ws2.Range("A:A"), 0)
If Not IsError(RowNo) Then
ws2.Rows(RowNo).Delete
End If

RowNo = Application.Match(CodeNo, ws3.Range("A:A"), 0)
If Not IsError(RowNo) Then
ws3.Rows(RowNo).Delete
End If
End If
Next r
End With
End Sub

Sunny

Avatar
Sean Hayes
Member
Members
Level 0
Forum Posts: 15
Member Since:
January 17, 2020
sp_UserOfflineSmall Offline
12
January 20, 2020 - 1:24 pm
sp_Permalink sp_Print

Thank you Sunny.

 

That works brilliantly

Avatar
Sean Hayes
Member
Members
Level 0
Forum Posts: 15
Member Since:
January 17, 2020
sp_UserOfflineSmall Offline
13
January 24, 2020 - 1:42 am
sp_Permalink sp_Print

Hi Sunny,

 

I tried running the above code on a test sheet.

When I have used it in my workbook I am having problems with it.

I have attached my workbook for you to look at and see how the above code could work in it if you do not mind.

The password for everything on the workbook is summerscales

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1461
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
14
January 24, 2020 - 10:57 am
sp_Permalink sp_Print

Hi Sean,

No workbook is attached.

Regards

Phil

Avatar
Sean Hayes
Member
Members
Level 0
Forum Posts: 15
Member Since:
January 17, 2020
sp_UserOfflineSmall Offline
15
January 24, 2020 - 3:21 pm
sp_Permalink sp_Print sp_EditHistory

My apologises there doesn't seem to be the workbook attached

Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 412
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
16
January 24, 2020 - 9:18 pm
sp_Permalink sp_Print

it looks like you have the worksheets protected so the macro cant delete anything.

you can get the macro to unprotect the work sheet by adding activesheet.unprotect "summerscales" to the beginning of the macro (just after 'Sub DeleteRow()') and then turning it back on with  activesheet.protect "summerscales" just before exit sub

Avatar
Sean Hayes
Member
Members
Level 0
Forum Posts: 15
Member Since:
January 17, 2020
sp_UserOfflineSmall Offline
17
January 25, 2020 - 5:52 am
sp_Permalink sp_Print

Thanks for your reply Purfleet.

I have the code working correctly as a standalone bit of code. My problem is I need it to run within my code that's already there in the 'Inventory Booked Out' sheet.

When I insert it into there I then experience problems with it. 

Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 412
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
18
January 25, 2020 - 7:43 am
sp_Permalink sp_Print

How are you putting the DeleteRow Sub in the existing ? are you pasting the whole code into a certain part of your existing code??

I noticed that you had the DeleteRow Sub code in module5, so i added a call DeleteRow at LN11 in the code on Sheet4 and apart from Inventory Booked in being protected seem to run okay (not checked output)

 

Private Sub Worksheet_Change(ByVal Target As Range)

'Unprotect Workbook
Sheets("Inventory Booked Out").Unprotect Password:="summerscales"
Sheets("Inventory Booked In").Unprotect Password:="summerscales"
Sheets("Scans with Date Time Stamp").Unprotect Password:="summerscales"
Sheets("Inventory List").Unprotect Password:="summerscales"

Call DeleteRow

Dim Item As String
Dim strDscrpt As String
Dim strPrice As String
Dim SearchRange As Range
Dim rFound As Range
Dim strBC As String

Avatar
Sean Hayes
Member
Members
Level 0
Forum Posts: 15
Member Since:
January 17, 2020
sp_UserOfflineSmall Offline
19
January 27, 2020 - 3:20 pm
sp_Permalink sp_Print

Thanks Purfleet for your reply and pointing out the obvious to me that I had stupidly missed. 

I didn't have 'Inventory Booked In' unprotected. Once I had included that all worked fine in how I already had it set to call DeleteRow.

Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 412
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
20
January 27, 2020 - 5:32 pm
sp_Permalink sp_Print

Hi Sean 

Glad it is working - i have found with vba that it is often the most simple things that cause the code to break - i once spent an hour trying to get xlcentre to work only to realise that vba needs the US spelling of Centre, changed to xlcenter and it worked.

Still kicking myself for that one!

Purfleet

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 170
Currently Online: mymalone, Riny van Eekelen, Ivan Kulubya
Guest(s) 71
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1431
Anders Sehlstedt: 845
Velouria: 574
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 213
A.Maurizio: 202
Aye Mu: 201
Jessica Stewart: 185
Newest Members:
Vicky Otosnika
Abhishek Singh
Kevin Sojourner
Kara Weiss
And Woox
Armani Quenga
moshood bello
annelies b
James1989
lucy gilmour
Forum Stats:
Groups: 3
Forums: 24
Topics: 6045
Posts: 26523

 

Member Stats:
Guest Posters: 49
Members: 31492
Moderators: 2
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Riny van Eekelen
© Simple:Press —sp_Information
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x