Macro to remove rows that don't contain a specific string in column B

[tisahardknocklife]

Hi,

I'm sure this is a commonly asked question, but I'd like this variant specifically for column B.

e.g.

If column B does not specifically contain "ART" (not case sensitive), then the entire row should be deleted.
Not left blank, but deleted.


All help appreciated

Thanks!

[Richard Buttrey]

Hi,

One way

Please Login or Register  to view this content.

[tisahardknocklife]

When I tried it on my workbook, I got "Can't complete requested operation" ?

[k1w1sm]

works okish here but will not delete row 1

[Richard Buttrey]

works okish here but will not delete row 1

I had assumed that row 1 would typically contain a column description header that you wouldn't want to delete.

Just remove the

.Offset(1, 0)
in the line
.Offset(1,0).SpecialCells(xlCellTypeVisible).Rows.Delete
to leave
.SpecialCells(xlCellTypeVisible).Rows.Delete

[k1w1sm]

I had assumed that row 1 would typically contain a column description header that you wouldn't want to delete.

Just remove the

.Offset(1, 0)
in the line
.Offset(1,0).SpecialCells(xlCellTypeVisible).Rows.Delete
to leave
.SpecialCells(xlCellTypeVisible).Rows.Delete

Not a problem. It was a nice solution, once I stepped through it and realised how it worked, I would have written a macro to loop through the lines. But I like yours better.

[Richard Buttrey]

As a general rule whenever you find yourself writing some loop code take a step back and reconsider. Loops, especially with large ranges have a big time overhead since every pass through the loop means jumping from VBA to the Excel App and then a return to VBA.

Often you can use an Autofilter - sometimes you may need to use a helper column with a formula that works out which rows should be identified for hiding, deleting, or copying, and then use a single line of code to address the whole block of filtered rows.

[Marc L]

Hi !

yes for big data I prefer to sort the data with a helper column and clear the block of rows to delete at once,
faster than deleting one by one …

[tisahardknocklife]

Hi,

I just realized that it's working (though I still get the message)
Is there any way that the rows I delete can apply to all sheets?

For example, if on sheet 1 (which the macro is run on) rows A B C are deleted, then rows A B C on all sheets are deleted?

[xladept]

Maybe this modification to Richard's code:

Please Login or Register  to view this content.

*But I don't know a way to guarantee that they are the same rows

[tisahardknocklife]

Well...I tried it and I got "cannot be applied to selected range" and it wiped out all other data except the first row in the other worksheets.

So that didn't work...

Is there a way to somehow record which rows are to be deleted then apply that to the other sheets?

[xladept]

This runs:

Please Login or Register  to view this content.