Delete rows VBA

kbp

New Member
Joined
May 14, 2012
Messages
36
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>
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,334
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
May 14, 2012
Messages
36
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,334
Office Version
  1. 2010
Platform
  1. Windows
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)...
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,453
Messages
5,528,875
Members
409,843
Latest member
akostaki
Top