Type Mismatch Error
 

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,

Type Mismatch Error
 

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

- Mangesh


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

Type Mismatch Error
 

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 *


"green67beanie" wrote in message
...
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,

Type Mismatch Error
 

Hi Green67beanie,

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

---
Regards,
Norman



"green67beanie" wrote in message
...
I guess I should have tried it before my previous acknowledgement. I get
the
same error with this code.

"Rob Bovey" wrote:


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 *


"green67beanie" wrote in
message
...
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,

Type Mismatch Error
 

"green67beanie" wrote in message
...
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" wrote:


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 *


"green67beanie" wrote in
message
...
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,

Type Mismatch Error
 

"green67beanie" wrote in message
...
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" wrote:

"green67beanie" wrote in
message
...
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" wrote:


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 *


"green67beanie" wrote in
message
...
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,

Type Mismatch Error
 

"green67beanie" wrote in message
...
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" wrote:

"green67beanie" wrote in
message
...
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" wrote:

"green67beanie" wrote in
message
...
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" wrote:


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 *


"green67beanie" wrote
in
message
...
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,

Type Mismatch Error
 

"green67beanie" wrote in message
...
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 *