Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 296
  • Last Modified:

Simple Excel VBA

I have a worksheet with a number of rows that will vary. I need a routine to delete all rows that do not have either an 11 or a 45 in the first cell of the row. The routine needs to stop when it gets to a blank row.
0
carlosab
Asked:
carlosab
  • 3
  • 3
1 Solution
 
DaveCommented:
In Column A?

A sample would help clarify yur questio

Cheers

Dave
0
 
carlosabAuthor Commented:
Yes, Column A. Here is a sample. Also, I need the script to delete any row where Column C does not start with CV. Thanks!
3      03-1-04      CV-0000059-11
3      03-1-04      CV-0000059-11
3      03-1-04      CV-0000060-11
11      11-1-01      CV-0000107-11
11      11-1-01      CV-0000108-11
11      11-1-01      CV-0000109-11
11      11-1-01      LT-0000098-11
43      43-3-02      LT-0000071-11
43      43-3-03      CV-0000043-11
43      43-3-03      CV-0000044-11
43      43-3-03      CV-0000045-11
43      43-3-03      CV-0000047-11
43      43-3-03      CV-0000047-11
45      45-1-05      LT-0000088-11
45      45-1-05      LT-0000089-11
45      45-1-05      LT-0000090-11
45      45-1-05      LT-0000090-11
0
 
DaveCommented:
hth

sample file attached

Cheers

Dave
Sub CullRows()
    Dim rng1 As Range
    Dim lngRow As Long
    Application.ScreenUpdating = False
    If [a2] <> vbNullString Then
        Set rng1 = Range([a1], [a1].End(xlDown))
    Else
        Set rng1 = [a1]
    End If
    For lngRow = rng1.Rows.Count To 1 Step -1
        If Not (Cells(lngRow, "A") = "11" Or Cells(lngRow, "A") = "45") Then
            Rows(lngRow).EntireRow.Delete
        Else
            If Left$(Cells(lngRow, "c"), 2) <> "CV" Then Rows(lngRow).EntireRow.Delete
        End If
    Next
    Application.ScreenUpdating = True
End Sub

Open in new window

cull.xlsm
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
carlosabAuthor Commented:
This is my dataset before running your code:

3      3/1/2004      CV-0000059-11
3      3/1/2004      CV-0000059-11
3      3/1/2004      CV-0000060-11
11      11/1/2001      CV-0000107-11
11      11/1/2001      CV-0000108-11
11      11/1/2001      CV-0000109-11
11      11/1/2001      LT-0000098-11
43      43-3-02      LT-0000071-11
43      43-3-03      CV-0000043-11
43      43-3-03      CV-0000044-11
43      43-3-03      CV-0000045-11
43      43-3-03      CV-0000047-11
43      43-3-03      CV-0000047-11
45      45-1-05      LT-0000088-11
45      45-1-05      LT-0000089-11
45      45-1-05      LT-0000090-11
45      45-1-05      LT-0000090-11


This is the remaining data after I run it:

11      11/1/2001      CV-0000107-11
11      11/1/2001      CV-0000108-11
11      11/1/2001      CV-0000109-11


0
 
carlosabAuthor Commented:
Sorry, I didn't realize that 45 was all LT in the 3rd column. Code works perfect. Thanks!
0
 
DaveCommented:
thx for the grade :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now