VBA - find and delete

[awsumchillicrab]

I have a data set that I text-to-column, after which I want to find cells that contain "material" and delete the entire row.

I have cells that look like " Material No.", or " Material No. ", or " Material N"...etc. So I'm going to do a xlpart kind of find.

And I want it to scan the entire column for all such rows, but without scanning beyond the last used row. Not sure if using a loop is necessary. I tried using loops but I can't get it to work cause I'm not too familiar with the FIND.

Help appreciated!

 

[kpark91]

So, you want to scan all the used range to find the word Material and delete the row?

Try

Application.ScreenUpdating = False
Dim LR&, i&, LC&, j&
LR = Range("A" & Rows.count).End(xlUp).Row
LC = Cells(1, Columns.count).End(xlToLeft).Column
 
For i = LR To 1 Step -1
    For j = LC To 1 Step -1
        If LCase(Cells(i, j).Value) Like "*material*" Then
            Cells(i,j).EntireRow.Delete
        End If
    Next j
Next i
Application.ScreenUpdating = True

Expect this code to run extremel slow if you have alot of rows to delete.
You must use a different algorithm to speed it up but the implementation is more annoying.

 

[awsumchillicrab]

wow, what a funky tool! - LIKE.
This works great.

For the sake of knowledge however, is it possible to build a faster model that uses FIND, since it seems to jump right at the necessary cell without explicitly scanning other cells.

 

[kpark91]

I'm not too sure what you mean by "explicitly scanning other cells"?

But I'll try to answer if I understood your question correctly.
The code is scanning all the used cells in Excel as it's scanning row by row and column by column.
What it does is it's looking into rows then columns meaning it will search through the range in this numbered order.

1  2  3  4  5  6  7
8  9  10 11 12 13 14
.
.

The code

Like "*material*"

is using same principle as FIND method you could find in the spreadsheet as * is a wild card character.


As for the faster code, I'm not too sure if the code really needs FIND as it only returns objects/cell address which may bring down the speed of the code and cannot know if the rows have already been deleted.
There are more reasons why FIND can be find in this application but I won't go in more since I don't think it's really neccessary to mention them.

Anyways, for the faster code, as Excel moves the formatting/properties of cells (which are vast and memory consuming) for each deletion of the entire row, a different algorithm is needed.
It is similar but makes a huge difference in runtime.
Here is the algorithm you could use:
1) Pinpoint the rows you want to delete
2) Sort them
3) Remove one large chunk rather than many little chunks.

 

[awsumchillicrab]

It's true using the FIND will be much more complex than the situation calls. But if a loop can be avoided, it will run through less code lines, particularly when debugging using F8. Getting to row 100 with the FIND takes 1 line of code and maybe 500 with a loop since it loops 99 times before getting there.

Sorting always scares the hell out of me.

 

[kpark91]

Just to let you know.
one line of code does not mean the computer is not looping through but it only makes the readability better.

Moreover, FIND only returns the first range object (way more than 2 bytes) as referenced in MS Help:
A Range object that represents the first cell where that information is found.

So, looping will be needed nonetheles..
However, if you want it, here is the code:
I cannot guarantee what it will do tho.

Dim LR&, LC&, Found As Range
LR = Range("A" & rows.count).End(xlUp).Row
LC = Cells(1, column.count).End(xlToLeft).Column
Found = Range("A1:"&Cells(LR,LC)).Find("Materials")
Do While Found <> Nothing
    Found.EntireRow.Delete
    LR = Range("A" & rows.count).End(xlUp).Row
    LC = Cells(1, column.count).End(xlToLeft).Column
    Found = Range("A1:"&Cells(LR,LC)).Find("Materials")
Loop