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

# 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
• 3
• 3
1 Solution

Commented:
In Column A?

A sample would help clarify yur questio

Cheers

Dave
0

Author 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

Commented:
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
``````
cull.xlsm
0

Author 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

Author Commented:
Sorry, I didn't realize that 45 was all LT in the 3rd column. Code works perfect. Thanks!
0

Commented:
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.