Delete rows VBA

[kbp]

Hi there

Need some help please.

I am trying to write a VBA macros in my huge data sheet. What I want is, column A has a lot of data starting with Alpha and Numeric text or even blank. I want to write the macros saying, if in Column A the text starts with the number "4", keep that row as is, and delete the rest of the rows in the sheet. Now the text starting with number 4 can be a 4 or a 5 digit number, that does not matter, but it always starts with 4. I wrote the below code, but it's doing the opposite. what am I doing wrong here? Thanks.

Macros:

Dim c As Range
Dim SrchRng

Set SrchRng = ActiveSheet.Range("A1", ActiveSheet.Range("A65536").End(xlUp))
Do
Set c = SrchRng.Find("4", LookIn:=xlValues)
If Not c Is Nothing Then c.EntireRow.Delete
Loop While Not c Is Nothing



Sample of data

DPR BC058-03 ONLINE MONETARY TRANSACTIONS BY OPERATOR PAGE 1
ACQ CORP :12 REPORT OF CLOSED BATCHES AS OF DATE: 06/10/14
DEPARTMENT :O 03131 CURRENCY CODE: 554 RUN DATE : 06/10/14
OPERATOR :AB342593
BATCH # TC RC CP ACCOUNT NUMBER MICROFILM REFERENCE CHECK NO AMOUNT DESCRIPTION REJ RSN
____________________________________________________________________________________________________________________________________
PLAN SEQ FIX PAY AMOUNT FIX PAY # STORE FL INT FL INS FL RATE
____________________________________________________________________________________________________________________________________
42047 71 13 12 4564-910-109-058-107 7-456491-4279-000-420-47001-4 0000000 15.00- LATE FEE REVERSAL
0.00 0 0.00 0.00 0.000000
42047 71 13 12 4564-910-133-488-106 7-456491-4279-000-420-47002-2 0000000 15.00- LATE FEE REVERSAL
0.00 0 0.00 0.00 0.000000
42047 71 13 12 4548-601-477-497-107 7-454860-4279-000-420-47003-8 0000000 15.00- LATE FEE REVERSAL
0.00 0 0.00 0.00 0.000000
42047 71 13 12 4548-601-133-907-127 7-454860-4279-000-420-47004-6 0000000 30.00- LATE FEE REVERSAL

<colgroup><col span="11"></colgroup><tbody>
</tbody>

 

[Rick Rothstein]

Is your data always structured as shown... rows starting with 4's contiguous with each other and always located at the bottom of the data?

 

[kbp]

No, not always structured but the rows that start with numbers anywhere between 40000 to 49999, we need to keep and the rest, it can be header rows, or lines or 0.00 or blank whatever needs to be deleted.

 

[Rick Rothstein]

Okay, depending on how huge your data sheet is, the following macro may work (if not, then it will take a little slower piece of code to do what you want)...

Sub KeepRowsStartingWith4()
  Dim Addr As String
  Addr = "A1:A" & Cells(Rows.Count, "A").End(xlUp).Row
  Range(Addr) = Evaluate("IF(ROW(),IF(LEFT(" & Addr & ")=""4""," & Addr & ",""""))")
  Range(Addr).SpecialCells(xlBlanks).EntireRow.Delete
End Sub

 

[kbp]

Perfect, thanks very much. It worked.