PDA

View Full Version : Solved: Find Multiple Entries Then Delete Row



f2e4
08-01-2008, 01:45 AM
Hey guys,

This is what I am using so far:


Dim x as integer
staffdropdown = activesheet.combobox6.value

x = columns(3).find(staffdropdown, after:=cells(3,3)).row

With Cells(x,3)

if .value = staffdropdown then
.entirerow.delete
end if
end with


This will look down column 3 and find a value I have selected from a combobox.

It does work but only for the first entry.

Does anyone know how I can expand the code above to find all entries and then delete these rows as well.

Thanks a lot,

F

Bob Phillips
08-01-2008, 02:12 AM
Dim cell As Range
Dim rng As Range
Dim FirstAddress As String

With ActiveSheet

staffdropdown = ActiveSheet.combobox6.Value

On Error Resume Next
With .Columns(3)

Set cell = .Find(staffdropdown, after:=.Cells(3, 1))
On Error GoTo 0
If Not cell Is Nothing Then

FirstAddress = cell.Address
Set rng = cell
Do

With cell

If .Value = staffdropdown Then

Set rng = Union(cell, rng)
End If
End With
Set cell = .FindNext(cell)
Loop Until cell Is Nothing Or cell.Address = FirstAddress

If Not rng Is Nothing Then rng.EntireRow.Delete
End If
End With
End With

f2e4
08-01-2008, 03:09 AM
As always, the code works a treat

Thanks bob.

P.S. I thought we should all stay away from loops as the are very time consuming - but this code is very fast!!!

Bob Phillips
08-01-2008, 03:15 AM
This is fast because it is using find, iut doesn't go through every row checking the cell value, and using built-in functionality is fast. Plus, I don't delet as I go, but save it all for one bit delete at the end. There is a loop, but only a small, control loop.

This technique works well unless you have a lot of rows that will get deleted, that is the rng variable gets lots of di-contiguous ranges union'ed with it. VBA can get quite inefficient then, but if the numbers are not bit, and we are talking many hundreds, maybe even thousands, it is fast as you say.