In theory, it should not matter that you have formulas in the cells, nor
what links the formulas have, because you are testing the cell value with
the If statement. The If statement should be only testing for true or
false. That is why I do not understand where the type mismatch is coming
from. It should just return false and go on to the next iteration. So, to
find out what is happening, you need to step through the procedure one line
at a time and check the value of your variables as you go. It is clear that
something is not what you expect it to be, or you would not be getting the
error message. Since, I do not get the error, I cannot come up with any
other answer.
"cardan" <(E-Mail Removed)> wrote in message
news:0b3c3fae-760f-4327-996d-(E-Mail Removed)...
On May 14, 8:47 am, cardan <carlsondan...@gmail.com> wrote:
> On May 13, 6:09 pm, "JLGWhiz" <JLGW...@cfl.rr.com> wrote:
>
>
>
> > I copied the code as revised by Wouter HM and it worked as intended,
> > without
> > error. It seems odd that you would get a type mismatch error on an
> > If...Then statement, since it only evaluates true or false. However, if
> > you
> > tried to type in corrections, it might be better if you just copy the
> > good
> > code and replace the one that is malfunctioning.
>
> > "cardan" <carlsondan...@gmail.com> wrote in message
>
> >news:afe6177c-4af0-49bb-b01d-(E-Mail Removed)...
> > On May 13, 12:53 pm, Wouter HM <wouter.ma...@sogeti.nl> wrote:
>
> > > Hi there
>
> > > You are close.
>
> > > In de code fragment Cells(a,b) the a refers to the row number and the
> > > b refers to the column number.
>
> > > Try:
>
> > > Sub DeleteRows()
> > > '
> > > ' DeleteRows Macro
> > > '
> > > ' Keyboard Shortcut: Ctrl+Shift+F
> > > '
> > > Dim WS As Worksheet
> > > Dim DeleteThese As Range
> > > Dim LastRow As Long
> > > Dim R As Long
>
> > > For Each WS In _
> > > Application.ActiveWindow.SelectedSheets
> > > Set DeleteThese = Nothing
> > > With WS
> > > LastRow = .Cells(.Rows.Count, 1) _
> > > .End(xlUp).Row
> > > For R = LastRow To 1 Step -1
> > > If .Cells(R, 1).Value = "DELETE" Then
> > > If DeleteThese Is Nothing Then
> > > Set DeleteThese = .Rows(R)
> > > Else
> > > Set DeleteThese = _
> > > Application.Union(DeleteThese, .Rows(R))
> > > End If
> > > End If
> > > Next R
> > > If Not DeleteThese Is Nothing Then
> > > DeleteThese.Delete
> > > End If
> > > End With
> > > Next WS
> > > End Sub
>
> > > HTH,
>
> > > Wouter
>
> > Hello. Thanks for the reply. I am still getting errors with this
> > macro. Specifically, "Run-time error '13': Type mismatch". When I
> > debug, it does highlight the row that you and EricG mentioned to
> > fix-
>
> > If .Cells(R, 1).Value = "DELETE" Then
>
> > I did correct my formula to reverse the .Cells(1, R)... to .Cells(R,
> > 1).....
>
> > Any suggestions?
>
> > Thanks again
>
> I think I have it working now. I am not sure of the exact problem but
> I changed the Ctrl+Shft+F shortcut to Ctrl+Shft+M. I think the Ctrl
> +Shift+F shortcut was being used someplace else, however I am not sure
> where since I don't have any other macros in it. Thank you for your
> help!
I may have spoken too soon. I tried the test in a new workbook and it
worked fine. However, when I put it into my workbook, I am still
getting the same run-time, mismatch error and it is still
acknowledging something is wrong with the " If .Cells(R, 1).Value =
"DELETE" Then" line. I have tried retyping it, copying and pasting,
removing the whole macro and pasting a copied version back in.
Could it be my setup? I usually "record" a macro and immediately stop
recording and the cut and paste it into. The macros are being pasted
in seperate Modules (Module 1, Module 2, etc...). Does this have
anything to do with it?
In my workbook, Column A is a set of formulas that link back to
another sheet that returns either the word "DELETE" or another word
(such as Midwest, Northwest). The data set is ~7400 rows long. Does
this matter?
|
Similar Threads
