VBA Code to keep only certain rows

[clau818]

Hello, novice in the VBA world and trying to put together some code that allows me to keep only certain rows in my data that meet certain criteria.

So I am pulling invoices from a program and the raw data gives me a bunch of unnecessary lines of information.

The rows I want to keep are the following;
If the cell in column A contains the text string "BIG"
I also want to keep the row following the row with the text string "BIG"
If the cell in column A contains the text string "IT1"
If the cell in column A contains the text string "N1" and the adjacent cell in column B contains the text string "BY"

All other rows can be deleted.

I need to be able to select all of the data in 2 or 3 invoices at once and run the code on my selected data only.

Thanks in advance for your help!!

 

[clau818]

Here is an example of what my data looks like for one invoice:

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

ISA

0

0

12

8.46E+09

1

7942915

U

401

0

P

>

GS

IN

8455389503

7942915

20161219

1054

X

004010VICS

ST

810

BIG

20161219

11017

5835281

TC#

568

TC&

Consolidated Invoice

REF

DP

294

REF

IA

5104603

N1

ST

92

569

N1

BY

92

569

ITD

1

3

4

30

DTM

11

20161206

IT1

4

74

EA

UP

889967090084

VA

25882

TC#

NONE

PID

F

adf

IT1

8

77

EA

UP

889967090312

VA

27079

TC#

NONE

PID

F

ghadg

IT1

23

86

EA

UP

889967090541

VA

30698

TC#

NONE

PID

F

adtre

IT1

30

59

EA

UP

889967086414

VA

30587

TC#

NONE

PID

F

asdfa

IT1

31

11

EA

UP

889967090343

VA

30587

TC#

NONE

PID

F

ereasd

IT1

32

19

EA

UP

889967090350

VA

30587

TC#

NONE

PID

F

hfhfs

IT1

34

49

EA

UP

889967090190

VA

30721

TC#

NONE

PID

F

dfadf

IT1

35

59

EA

UP

889967090039

VA

30721

TC#

NONE

PID

F

zvvzxcv

IT1

36

39

EA

UP

889967090046

VA

30721

TC#

NONE

PID

F

zvzcv

IT1

38

66

EA

UP

889967090206

VA

30739

TC#

GOLD

NONE

PID

F

zcvzcfe

TDS

0

0

CAD

M

GBEA

BM

92450630

ISS

0

CA

0

LB

CTT

10

SE

45

GE

1

IEA

1

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

 

[Joe4]

Try this:

Sub MyDeleteRows()

    Dim lastRow As Long
    Dim myRow As Long
    Dim deleteRow As Boolean
    
    Application.ScreenUpdating = False
    
'   Find last row in column A with data
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows backwardS, up to row 2
    For myRow = lastRow To 2 Step -1
        deleteRow = True
'       Check for "BIG" on current row
        If InStr(Cells(myRow, "A").Value, "BIG") Then
            deleteRow = False
        Else
'           Check for "BIG" on previous row
            If InStr(Cells(myRow - 1, "A").Value, "BIG") Then
                deleteRow = False
            Else
'               Check for "IT1" on current row
                If InStr(Cells(myRow, "A").Value, "IT1") Then
                    deleteRow = False
                Else
'                   Check for "N1" and "BY" on current row
                    If InStr(Cells(myRow, "A").Value, "N1") And _
                        InStr(Cells(myRow, "B").Value, "BY") Then
                        deleteRow = False
                    End If
                End If
            End If
        End If
'       Delete row if any condition met
        If deleteRow Then Rows(myRow).Delete
    Next myRow
    
    Application.ScreenUpdating = True
    
End Sub

 

[clau818]

wow, you are brilliant! thanks so much! I was so far off from this, no chance I would've figured it out. Thanks for the comments as well! slowly but surely figuring this stuff out.

One thing, it's running the code for my entire sheet. Is it possible to just have it run on the data that I select? Or will that require completely different code? If not, it's fine I can just import the raw data to a new sheet every time and copy it to my master sheet where I have all my previous invoices on.

 

[clau818]

One other thing, I would like the invoices to be separated by a space. After the code runs I would like to insert 2 rows right above the row "BIG" row. Having some trouble putting this together as well.

Again, thanks again for everything you sent me already!

 

[Joe4]

A few modifications to have it run against the selected range:

Sub MyDeleteRows()

    Dim firstRow As Long
    Dim prevRow As Long
    Dim lastRow As Long
    Dim myRow As Long
    Dim deleteRow As Boolean
    
    Application.ScreenUpdating = False
    
'   Find find and last row of selected range
    firstRow = Selection.Rows(1).Row
    lastRow = Selection.Rows.Count + firstRow - 1
    
'   Loop through all rows backwardS, up to row 2
    For myRow = lastRow To firstRow Step -1
        deleteRow = True
'       Find previous row (default to 1 if top row)
        prevRow = myRow - 1
        If prevRow = 0 Then prevRow = 1
'       Check for "BIG" on current row
        If InStr(Cells(myRow, "A").Value, "BIG") Then
            deleteRow = False
        Else
'           Check for "BIG" on previous row
            If InStr(Cells(prevRow, "A").Value, "BIG") Then
                deleteRow = False
            Else
'               Check for "IT1" on current row
                If InStr(Cells(myRow, "A").Value, "IT1") Then
                    deleteRow = False
                Else
'                   Check for "N1" and "BY" on current row
                    If InStr(Cells(myRow, "A").Value, "N1") And _
                        InStr(Cells(myRow, "B").Value, "BY") Then
                        deleteRow = False
                    End If
                End If
            End If
        End If
'       Delete row if any condition met
        If deleteRow Then Rows(myRow).Delete
    Next myRow
    
    Application.ScreenUpdating = True
    
End Sub