Delete selected rows in Excel

Hello,

In the below code lines I get a type mismatch error message when I try
to use variables inside
the quotes, I need to programmatically stuff in variables if my script
is going to work, like this

objExcel.Rows("intRow:intLastRow).Select() 'Doesn't work.
objExcel.Rows("44:91").Select() 'Works.
objExcel.Selection.Delete

Seems only regular numbers works inside the quotes.

Any idea of what to do ?

TIA

Svein,Oslo

Dave
Sun Apr 09 18:18:40 CDT 2006

Try;

Option Explicit
Dim filePath, oExcel, oSheet, intLastRow, intRow
Const xlUp = -4162

intRow = 3
intLastRow = 6
filePath = "c:\Test.xls"

Set oExcel = CreateObject("Excel.Application")
'oExcel.Visible = True
oExcel.Workbooks.Open filepath
Set oSheet = oExcel.ActiveWorkbook.Worksheets(1)
oSheet.Rows("" & intRow & ":" & intLastRow & "").Delete xlUp

oExcel.DisplayAlerts = False
oExcel.ActiveWorkbook.SaveAs filePath
oExcel.ActiveWorkbook.Close
oExcel.Quit
set oSheet = Nothing
Set oExcel = Nothing


FYI;
you don't want () after Select

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

<svein.dale@networks.no> wrote:
|
| Hello,
|
| In the below code lines I get a type mismatch error message when I try
| to use variables inside
| the quotes, I need to programmatically stuff in variables if my script
| is going to work, like this
|
| objExcel.Rows("intRow:intLastRow).Select() 'Doesn't work.
| objExcel.Rows("44:91").Select() 'Works.
| objExcel.Selection.Delete
|
| Seems only regular numbers works inside the quotes.
|
| Any idea of what to do ?
|
| TIA
|
| Svein,Oslo
|

svein
Mon Apr 10 10:32:09 CDT 2006

Hi again,

Thx for quick reply, it seems to be, as your example shows, an issue
with my variables being integer/numbers
and not string which is required. I got this tip and it also worked :

strRowString = CStr(intRow) & ":" & CStr(intLastRow) '
Which converts to string !!
objExcel.Rows(strRowString).Select
objExcel.Selection.Delete

Again thx !

Svein

Dave
Mon Apr 10 10:41:27 CDT 2006

No, that's not it. All variables are variant. The line you posted doesn't
work for more than one reason.

objExcel.Rows("intRow:intLastRow).Select()

() after Select
missing closing quote
incorrect formatting and syntax

(not picking, just trying to help)

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

<svein.dale@networks.no> wrote:
| Hi again,
|
| Thx for quick reply, it seems to be, as your example shows, an issue
| with my variables being integer/numbers
| and not string which is required. I got this tip and it also worked :
|
| strRowString = CStr(intRow) & ":" & CStr(intLastRow) '
| Which converts to string !!
| objExcel.Rows(strRowString).Select
| objExcel.Selection.Delete
|
| Again thx !
|
| Svein
|