Rob,

Union gets very inefficient as the number of matches grows.

I don't use i for integer and so on, I use
i - index
c - count (hence I can't use c for constants)
n - number (some numeric variable that is neither integer or count)
o - for object (sometimes I go oRngxxx, oWsxxx, etc.)
s - general strings
k - for constants.

etc.

It's my version of Hungarian. I generally feel that it doesn't help to know
what datatype is being used, it's far better to know what it is being used
for. Don't know about Fortran, never used it, Algol, Plan, Pascal, Cobol,
yes, Fortran no.

Regards

Bob

"Rob van Gelder" wrote in message
...
Very clever.

Criteria1 would need to be "*f*" if it were to delete rows containing f.

My fastest approach would have been to union matched rows and do a final
delete at the end.
Altering worksheets for the purpose of matching just isn't my style - each
to their own I guess.

Any reason behind the k in kSearch? Is this like Fortran days when i and
j
were for Integers? Just curious.

Cheers!

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Bob Phillips" wrote in message
...
Here's an alternative which is substantially quicker on a large dataset

Sub test()
Const kSearch = "f"

With ActiveSheet
.Range("B1").EntireRow.Insert
.Range("B1").Value = "test"
.Columns("B:B").AutoFilter Field:=1, Criteria1:=kSearch
.Cells.SpecialCells(xlCellTypeVisible).EntireRow.D elete
.Range("B1").EntireRow.Delete

End With
End Sub



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Rob van Gelder" wrote in
message
...
Q1: The syntax looks fine. I wonder if you don't have a cell selected
(maybe
a chart instead) or your workbook is not visible or something?

Q2: I posted this code earlier. It deletes rows which contain the
letter
f
I'm not 100% certain what you mean by delete blank cells. Do you mean
blank
rows after matching 'f' or just all blank rows?

Sub test()
Const cColumn = 2, cSearch = "f"
Dim i As Long, lngLastRow As Long

With ActiveSheet
lngLastRow = .Cells(Rows.Count, cColumn).End(xlUp).Row

For i = lngLastRow To 1 Step -1
If InStr(1, .Cells(i, cColumn).Value, cSearch) 0 Then
.Rows(i).Delete xlShiftUp
End If
Next
End With
End Sub



--
Rob van Gelder - http://www.vangelder.co.nz/excel


"ksnapp " wrote in message
...
Hello,

question 1:

can somebody please explain to me what the type mismatch runtime
error
means? Here is the code that gets Highlighted when I run the sub.

If ActiveCell = "" then

I don't yet have the help file for VBA installed and can't make the
sub
work becuase i don't understand

question 2:

I need to make another sub that will search through a column and
delete
rows that countain certain text and the blank cells beneth them, any
takers?


thank you


---
Message posted from http://www.ExcelForum.com/