Type Mismatch Error

[Guest]

For the following code:

Selection.End(xlDown).Select
FFirstRw = Application.WorksheetFunction.Text(ActiveCell.Row + 1, 0)
FLastRw = Application.WorksheetFunction.Text(ActiveCell.Row + 3, 0)
Rows("FFirstRw:FLastRw").Select
Selection.Delete Shift:=xlUp

Both FFirstRw and FLastRw are defined as Strings. I am getting the following
error for the fourth command.

Run time error '13'
Type mismatch

I have tried it without the TEXT workbook function conversion and still get
the same error. What do I need to do? Thanks,

 

[mangesh_yadav]

try Rows(FFirstRw & ":" & FLastRw).Select

- Mangesh

 

[Rob Bovey]

This would be a bit shorter:

Selection.End(xlDown).Select
Rows(CStr(ActiveCell.Row + 1) & ":" & CStr(ActiveCell.Row + 3)).Delete
Shift:=xlUp

It assumes that the first line doesn't bring you all the way down to the
bottom of the worksheet (i.e. row 65536).

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

 

[Norman Jones]

Hi Green67beanie,

The solutions suggested by Rob and Mangesh both worked for me.

 

[Rob Bovey]

I guess I should have tried it before my previous acknowledgement. I get the
same error with this code.

Make sure that the first line of code is not bringing you all the way to
the bottom of the worksheet. Update your code as follows:

Selection.End(xlDown).Select
MsgBox Selection.Address

If the message box shows that your selection is in row 65536, this is the
problem.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

 

[Rob Bovey]

Actually, I have the code inside the following Do While:

Do While (ActiveCell.Row < SLastRow)
Selection.End(xlDown).Select
Rows(CStr(ActiveCell.Row + 1) & ":" & CStr(ActiveCell.Row +
3)).Delete Shift:=xlUp
Loop

SLastRow is defined as a Long

That doesn't tell you whether the Selection.End(xlDown).Select is
bringing you to the bottom of the worksheet, which I suspect it is. Choose
the Debug button when you get the Type Mismatch error then hover your mouse
cursor over ActiveCell.Row and see what it says.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

 

[Rob Bovey]

OK, the row number is in the middle of the active range, Row 43, to be

exact.

I have no idea why that would be causing a type mismatch error on the
Rows.Delete line. Are you sure that's the specific line where the error
occurs? You can run the test procedure below to verify that 43 is a valid
number for ActiveCell.Row in that line of code:

Sub Test43()
Rows(CStr(43 + 1) & ":" & CStr(43 + 3)).Delete Shift:=xlUp
End Sub

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

 

[Rob Bovey]

That ran perfectly. That led me to break out the section of code into its own
With, and things work great now. Thanks so much for sticking with me to
resolve this.

Glad you got it working!

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *