deleting rows from lists 2

[duncansancho]

I frequently produce lists which then need pruning according to adjacent information and would like to know the quickest and most efficient way of doing this with VBA.
For example I may have a column of 6000 entries in column A, and must remove all entries next to a "1" in column B. At present my code is as follows but it is slow and inefficient
Range("A1&quot.Select
Do Until ActiveCell = ""
Do While ActiveCell.Offset(0, 1) = 1
Selection.EntireRow.Delete
Loop
ActiveCell.Offset(1, 0).Range("A1&quot.Select
Loop

Any help much appreciated
Duncan

 

[dyarwood]

Try

i = 1

Do Until IsEmpty(Cells(i,1))
If Cells(i,2).Value = 1 Then
Rows(i).Delete
Else
i = i+1
End If
Loop

This will cycle down the first column until it is empty and if the cell in row 'i' column B is equal to one it deletes the row and does not move to the next one (in case this new row contains has 1 in column B) otherwise it moves to the next row. The select slows the process down.

let me know if that helps.

dyarwood

 

[duncansancho]

dyarwood
Thanks for the effort but I can't get it to work! How does this code know where to begin and which column to process?
Duncan

 

[dyarwood]

i is the row index. i = 1 means that it will start on row 1

Cells(i,1) means The cell in row 'i' (in this case 1) and column 1.

The code does the action in the do loop until the cell in column 1 and row i (not necessarily 1 now) is empty.

If the IF statement is true (the cell in column 2/B and row 'i' is equal to 1) then the row is deleted. i is not changed as the row which moves up may be needing to be deleted.

If the IF statement is false then i is increased by one and the loop starts again.

You may need this instead of Rows(i).Delete

Rows(i).Deleted Shift:=xlUp

 

[TonyJollans]

Hi duncansancho,

You can do this much more easily (and quickly) with an autofilter ..

Columns("B").AutoFilter Field:=1, Criteria1:="1"
Range("B2:B60000").SpecialCells(xlCellTypeVisible).EntireRow.Delete
Columns("B").AutoFilter

' Set Autofilter off

Note that the range of the Delete must start in row 2, or row 1 (which is assumed to have headings) will be deleted regardless of contents.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[xlbo]

Tony - you do realise that you don't need the specialcells qualifier for this.....

This will do the same:

lRow = Cells(65536, 1).End(xlUp).Row
Columns("B&quot.AutoFilter Field:=1, Criteria1:="1"
Rows("2:" & lRow).EntireRow.Delete
Columns("B&quot.AutoFilter

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[duncansancho]

Thanks to both of you - one more thing!, How would I express it if I wanted the criterial to be over 0 (in other words any number)?
Many thanks, Duncan

 

[xlbo]

Columns("B&quot.AutoFilter Field:=1, Criteria1:=">0"

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[duncansancho]

Wonderful!!
Thanks a lot.
Duncan

 

[TonyJollans]

Hi Geoff,

Been out for a few hours and come back to find you've done the best work and I've got the star

Of course you are right; for what it's worth here's what I did: I typed in the code in Excel just to make sure what I was posting was valid - and then posted something different (some things I never learn!). Trouble was I tested it with the whole column (under which circumstances, of course, you DO need the special cells bit) and then just changed the selection for the Delete in the post to allow for the headings. My code wasn't wrong, it was just less elegant (and probably less efficient) than it could have been. That's my excuse (and I'm sticking to it!)

Happy New Year to you,

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[xlbo]

No worries Tony - thought it might be summat like that. Happy New Year to you also

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[duncansancho]

Guys
I thought I'd given you both a star, so here is one to even it up! Thanks to you both
Duncan