Hello,

question 1:

can somebody please explain to me what the type mismatch runtime erro
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 su
work becuase i don't understand

question 2:

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


thank yo

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

For the first question.

you need if activecell.value = "" then

for the second question.. you can do some search with google and you'll
get hundreds of posts.

Regards,

Cesar Zapata


ksnapp < wrote:

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/

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/

the thing is that i get that error message after the sub has run fine
few tries. I have added the .value part and it makes no difference

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

..Value is the default property, so I'm not suprised it still fails.

Perhaps you could post the code which is (eventually) generating the error?

Even better, you could create a test copy and start removing lines until you
get the minimum amount of code that will generate the error. Then post that
if you're still stuck.

Fixing broken code makes you a better programmer. In my opinion, the
debugger in Office VBA is the best debugger for any IDE ever.


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


"ksnapp " wrote in message
...
the thing is that i get that error message after the sub has run fine a
few tries. I have added the .value part and it makes no difference.


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

"Cesar Zapata" wrote in message ...
For the first question.

you need if activecell.value = "" then

Not correct. Value is the default property, which means that if no property is present, VBA assumes the default. It is better IMO to not rely on defaults, but it is not incorrect.

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/

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/

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/