several questions

  • Thread starter Thread starter ksnapp
  • Start date Start date
K

ksnapp

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
 
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
 
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
 
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
 
..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.
 
Cesar Zapata said:
For the first question.

you need if activecell.value = "" then
Click to expand...

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.Delete
.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 said:
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
Click to expand...
 
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 said:
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.Delete
.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)
Click to expand...
 
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
 
Back
Top