• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA - Delete all rows with zero value in particular column

C

Carlos Ruano

Member
Hi,

I would like to be able to delete all rows of data which have 0.00 in the fifth column. The column header is "Hrs per Position", and the sheet name is "GPT to PlanView Mapping". The number of rows will change from time to time.

I found the following code, but it is not working for me, and I do not know how to fix it.

Option 1:

Sub Format()

Dim Fund As Long
Dim TotalLoop As Long

RowCount = Range("A65536").End(xlUp).Row
For TotalLoop = RowCount To 2 Step -1
Fund = Cells(TotalLoop, 10)
If Fund = 0 Then
Rows(TotalLoop).Delete
Cells(TotalLoop - 1, 10) = Fund
End If
Next TotalLoop

End Sub

Option 2:

Sub Format()

Dim sh As Worksheet
Dim Fund As Long
Dim TotalLoop As Long
Set sh = ThisWorkbook.Sheets("Sheet1")
RowCount = sh.Range("A1", sh.Range("A1").End(xlDown)).Rows.Count


For TotalLoop = RowCount To 2 Step -1
Fund = Cells(TotalLoop, 10)
If Fund = 0 Then
Rows(TotalLoop).Delete
End If
Next TotalLoop

End Sub

Any help would be greatly appreciated!!

Carlos
 
Carlos Ruano
1) Have a backup!
2) Verify that You really have a backup!
3) If don't want to verify act then take then 1st "'" away from 1st line
4) If You have more than five columns there
then change F to the most right column letter!
I tested only with five columns!
5) Run it!
Code: 
Sub CarlosRuano()
'    Application.DisplayAlerts = False
    With Sheets("GPT to PlanView Mapping")
        If .FilterMode Then .ShowAllData
        y = .Cells(.Rows.Count, 5).End(xlUp).Row
        .Range("A1:F" & y).AutoFilter Field:=5, Criteria1:="0"
        f = .Cells(.Rows.Count, 5).End(xlUp).Row
        If f > 1 Then .Range("A2:F" & y).Delete
        If .FilterMode Then .ShowAllData
        .Range("A1:F" & y).AutoFilter
    End With
    Application.DisplayAlerts = True
End Sub
 
Last edited:
Hi vletm,

Thank you for the quick reply and the code. I had to change all of your F's to E's since E is the fifth column. At least, I think I had to do that!

The code runs, but then gives me the following error message:

Run-time error '1004': ShowAllData method of Worksheet class failed

The data on the sheet is left filtered, and when I manually unfilter the data, everything is gone.

Any ideas?
 
Carlos Ruano - yes!
I hope that You did those 1 & 2 too!
Do You have something in the 1st row?
There should be something in [E1] like header.
I tested that code again and it works here - of course!
Can You send sample of that file here?
 
vletm said:
Carlos Ruano - yes!
I hope that You did those 1 & 2 too!
Do You have something in the 1st row?
There should be something in [E1] like header.
I tested that code again and it works here - of course!
Can You send sample of that file here?
Click to expand...

Hi vletm, yes, I have attached a sample. Thank you for your time!
 

Attachments

  • vletm.xlsx
    342.2 KB · Views: 4
I also noticed one more thing. The > should be changed to <
Changing this solves the problem of all the data being deleted, but the other error still occurs.
 
Carlos Ruano - yes
... and You just forget to write that it is table!
So ... I had to change mode!
Code: 
Sub CarlosRuano()
    Application.ScreenUpdating = False
    With Sheets("GPT to PlanView Mapping")
        y_max = .Cells(.Rows.Count, 5).End(xlUp).Row
        If y_max > 1 Then
            For y = y_max To 2 Step -1
                If .Cells(y, 5) = 0 Then .Rows(y & ":" & y).Delete
            Next y
        End If
    End With
    Application.ScreenUpdating = True
    MsgBox "Done"
End Sub
 
Hello Carlos...

You can also ask user prefered column to remove...With input box.

@vletm .... Modified code..

Code: 
Sub CarlosRuano()
Dim inp as integer
Inp=inputbox("please enter column Number")
    Application.ScreenUpdating = False
    With Sheets("GPT to PlanView Mapping")
      y_max = .Cells(.Rows.Count, inp).End(xlUp).Row
        If y_max > 1 Then
            For y = y_max To 2 Step -1
                If .Cells(y, inp) = 0 Then .Rows(y & ":" & y).Delete
            Next y
        End If
    End With
    Application.ScreenUpdating = True
    MsgBox "Done"
End Sub
 
vletm said:
Carlos Ruano - yes
... and You just forget to write that it is table!
So ... I had to change mode!
Code: 
Sub CarlosRuano()
    Application.ScreenUpdating = False
    With Sheets("GPT to PlanView Mapping")
        y_max = .Cells(.Rows.Count, 5).End(xlUp).Row
        If y_max > 1 Then
            For y = y_max To 2 Step -1
                If .Cells(y, 5) = 0 Then .Rows(y & ":" & y).Delete
            Next y
        End If
    End With
    Application.ScreenUpdating = True
    MsgBox "Done"
End Sub
Click to expand...

Good Day,

Unfortunately, this code is not working for me. It is not causing any errors, but it seems to just keep running and running and .... you get the point. I will have a co-worker try it and see if they have luck.
 
Guess what!? I sent the worksheet and the code to my co-worker, and it works perfectly for her! This is a mystery for sure.

Anyway, thank you vletm for your time and expertise!! Thank you also, Monty for your addition!
 
It works! It works!!

I had to perform a very advanced operation to get it to work. It might be above all of your heads, but I'll try to explain. I restarted my computer :) Only very high-level computer experts can perform this operation. I'm just glad I'm one of them.

Thanks again for all of your help!

Carlos
 
Carlos Ruano
... restarting computer ...
it's so normal way to 'fix' computer challenges
it is as normal as everyday eating!
(... or mention of using tables ;) )
Try to remember next time
 
You must log in or register to reply here.
Back
Top