• 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.

Delete row if...

I

Imagrump

New Member
The following code:
Code: 
Sub Delete_zero_row()
With Columns("G")
    .AutoFilter Field:=1, Criteria1:="0" 'remove the quotes around the zero if it's not text
    .Resize(Rows.Count - 1).Offset(1).EntireRow.Delete
    .AutoFilter
End With
End Sub
does what it is supposed to do; it deletes rows which contain a zero in column G. I do this once a week and there are an average of about 6,000 to 8,000 rows.
I want to delete rows in column H which contain a number less than 1100. Nothing that I am doing will work.

For informational purposes: I know nothing of vba and not much more about Excel. I have been using spreadsheets for some time but very basically. Now I need to speed some of my work up and I need to start using vba or something. By the way, this code is for an imported csv file which must be edited before ending up in an Access database.

If you can help, I will appreciate it.
THANKS !!!
 
Last edited by a moderator:
Here is code to delete rows as requested.

Option Explicit
Code: 
Sub DelH()
    Dim i As Long, lr As Long
    lr = Range("H" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    For i = lr To 1 Step -1
        If Range("H" & i) < 1100 Then
            Range("H" & i).EntireRow.Delete
        End If
    Next i
    Application.ScreenUpdating = True
    MsgBox "complete"

End Sub
 
How about
Code: 
Sub Imagrump()
   With Columns("G")
      .AutoFilter Field:=1, Criteria1:="0"
      .Resize(Rows.Count - 1).Offset(1).EntireRow.Delete
      .AutoFilter
   End With
   With Columns("H")
      .AutoFilter Field:=1, Criteria1:="<1100"
      .Resize(Rows.Count - 1).Offset(1).EntireRow.Delete
      .AutoFilter
   End With
End Sub
 
Alan: The one try I did yesterday worked. I cannot believe that you got your response posted so quickly. I posted just before I was going out and by the time I got my computer ready to shut down you had posted! 2 minutes!! Today I will try it a couple more times to make absolutely certain....yesterday's was kind of quick because of the circumstances. THANKS !!!

Fluff: I have no idea if your code will work because it was on the computer when I turned it on this AM. I will definitely try it because applying the code once instead of twice has to be better. I have to make sure that the rows are removed sequentially so as not to conflict. THANKS !!!

Mark: I have no idea what your comment means....please explain.

"Last edited by a moderator: Today at 2:42 AM" Can someone explain this to me? I read the explanation but "it does not compute".

THANK YOU! THANK YOU!
 
Fluff:
Your code works like a charm; I was able to use it this AM to edit my csv file.

I was without internet for a couple of days so I got a little behind.

Now I need to mark all of these as SOLVED since they all work.

THANKS !!!
 
You must log in or register to reply here.
Back
Top