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/