[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 294
  • 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
 
Dave BrettCommented:
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
 
Dave BrettCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
Dave BrettCommented:
thx for the grade :)
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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