How to delete rows that match either of two criteria, one involving an array

[dh490311]

Every day I get a large worksheet with thousands of rows (each day it's a different number of rows), only 50-60 of which I'm actually concerned about. I've been using interactive filtering to limit my view to just the relevant rows, but the row numbers don't change, so that suggests to me that all the irrelevant rows are still in the spreadsheet, they're just hidden. I'd very much like to actually delete the irrelevant rows, since I need to send the relevant subset to other people via email and the resulting attachments are currently huge, and I'd like to do it in a macro, since it's a task I do daily. But I'm an absolute novice at VBA and need help with the code.

I want to delete any row that matches either of these two criteria:

EITHER

1. Column A is blank.

OR

2. The text in Column B does NOT include any of the five product codes I'm concerned with. Column B contains both the product code and the long text name of the product, so I'd rather not have to test on the full text of the contents of Column B; I'd just like to check to see if any of my product codes is in there. I *could* test on the full contents if I need to; I'd just rather not. If Column B contains one of my product codes, I need to keep the row and not delete it.

In another thread from 2011 I found some code to test for partial values in an array, but I couldn't figure out how to add my second criterion and delete the entire row if either criterion was met.

Thanks very much, in advance, for any help.

 

[Leith Ross]

Hellodh490311,

Can you post a few examples of the product codes?

 

[mirabeau]

You can try this VBA code.
Your product codes are in about the 4th line Prodcode = Array("prco1", ...
Replace the prco1 etc. by your actual codes before running the VBA.
Or you can have them included however else you'd like specified, like inputbox, entries in a worksheet, etc.

Sub criteria_delrows()

Dim Prodcode, a, u()
Dim rw As Long, cl As Long, i As Long, j As Long

Prodcode = Array("prco1", "prco2", "prco3", "prco4", "prco5")
rw = Cells.Find("*", , , , xlByRows, xlPrevious).Row
cl = Cells.Find("*", , , , xlByColumns, xlPrevious).Column
ReDim u(1 To rw, 1 To 1)
a = Cells(1).Resize(rw, cl)

For i = 1 To rw
    x = 0
    If Len(a(i, 1)) = 0 Then
        u(i, 1) = 1
    Else
        For j = 0 To UBound(Prodcode)
            If InStr(a(i, 2), Prodcode(j)) > 0 Then x = 1: Exit For
        Next j
        If x = 0 Then u(i, 1) = 1
    End If
Next i

Cells(cl + 1).Resize(rw) = u
Cells(1).Resize(rw, cl + 1).Sort Cells(cl + 1)
If Cells(cl + 1).End(4).Row < Rows.Count Then _
    Range(Cells(1), Cells(cl + 1).End(4)).Delete xlUp

End Sub

 

[dh490311]

Can you post a few examples of the product codes?

Sure. They are all 4-digit integers, but in the spreadsheet they are in a text column, not numeric:
"4349", "4379", "4380", "4382", "4363"

 

[dh490311]

Thank you, mirabeau, for the code! It *almost* works perfectly! The original spreadsheet has every row the same height, enough for one line of text. After the macro runs, almost every row consumes several inches of vertical space, with the actual contents of the row at the bottom. Double-clicking the boundary between rows at the far left edge of the spreadsheet does not shrink the row to the single line it should be. There are a handful of rows that are the correct height, but I can't tell what's different about those. Is there any way to edit the routine so that every row consumes only one vertical line after all the row deletions are complete?

Thanks again!

 

[mirabeau]

Thank you, mirabeau, for the code! It *almost* works perfectly! The original spreadsheet has every row the same height, enough for one line of text. After the macro runs, almost every row consumes several inches of vertical space, with the actual contents of the row at the bottom. Double-clicking the boundary between rows at the far left edge of the spreadsheet does not shrink the row to the single line it should be. There are a handful of rows that are the correct height, but I can't tell what's different about those. Is there any way to edit the routine so that every row consumes only one vertical line after all the row deletions are complete?

Thanks again!

there's nothing in that code that should do anything to row heights.

I've written a lot of VBA codes and never had that happen before, unless deliberately.

It's hard for me to comment on the reason without being able to reproduce that effect, and personally I rather track down the cause than just adjust for it.

But you can try the adding the following line at the start or end of the code or even in a new code (maybe try both)

ActiveSheet.Rows.EntireRow.AutoFit

and if that still gives unacceptable spacing then try forcing the row heights to be what you want by something like

ActiveSheet.Rows.RowHeight = 16

again either at start or end of the code.

 

[dh490311]

mirabeau: Thank you again! The AutoFit version didn't work, but setting the row height to a specific number did, and the whole macro is also *fast*! I would much rather address the root cause, too, but results trump elegance when it would take much too long to track down the root cause. I can't tell you how much I appreciate your help!

--Dan

 

[mirabeau]

mirabeau: Thank you again! The AutoFit version didn't work, but setting the row height to a specific number did, and the whole macro is also *fast*! I would much rather address the root cause, too, but results trump elegance when it would take much too long to track down the root cause. I can't tell you how much I appreciate your help!

--Dan

Dan,

Good that you're happy with the outcome and glad that I was of some help.

The anomalous row heights could be something to do with your hidden rows, cell alignment set to word wrap, lots of non-printing characters somewhere in the sheet, or other.

I usually test my codes starting with a totally blank worksheet and generate my own test data. This often doesn't reflect the real workplace, in that many worksheets may have had a lot of prior use for whatever purpose before a code only tested on a fresh sheet is used.

Problems can nearly always be tracked down, given time and persistence, but one has to always consider whether the effort is worth the result.

Anyway, good luck!

 

[dh490311]

mirabeau: You gave me the crucial clue! I had a column that I was just hiding that had multiple lines of text in most rows. When I delete that column instead of hide it, all the rows are the right size. An added benefit is that the spreadsheet is even smaller without the unnecessary hidden column. Thanks again!