Delete Excess rows - type mismatch error 2

[JSMITH242B]

I hope someone is able to help me out with this one..

Bit of background...I have 4 Excel Templates which I've formatted. I have formatted 3000 rows - not expecting more records than this for each one - validation is taken care of by the calling program. Headings and Columns are different in each template.
The 3001th row in each template is a summary/total of all the previous rows.
If there are only 10 records, I would like to suppress the extra rows..

I have the following code(snippet):

intRange = intRows + 6 'previous rows are headings
strRange = intRange & ":3000"


.deleteColumns srange - (calls the procedure below)

public Sub deleteColumns(srange)
'delete the range of columns selected
ExcelApp.Columns(srange).Select
ExcelApp.Selection.Delete 1
end sub

However when the deleteColumns is called, I receive a 'type mismatch' error.
On inspecting the intrange and strRange values, these are 'correct' based on the array being passed.

If I had 45 records, intRange is 51 -this is correct
strRange is 52:3000. This is the range I would like to delete.

I tried calling deleteColumns using the following:

deleteColumns "A1:B20" however, I still get the type mismatch error.

Any help much appreciated.

regards
J

 

[gh61]

Perhaps try deleting rows rather than columns?
.Rows(strRange).Delete


G

 

[PHV]

Have you tried this ?
ExcelApp.Range(srange).Rows.Delete

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[PHV]

Have you tried this ?
ExcelApp.Rows(srange).Delete

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[JSMITH242B]

Thank you gh61, PHV. I'll give this a go..

J

 

[JSMITH242B]

Hello PHV and gh61.
I'm still getting the type mismatch error.

I changed
ExcelApp.Columns(srange).Select to:

ExcelApp.Range(srange).Rows.Delete

I also tried changing it to:
ExcelApp.Rows(srange).Select
ExcelApp.Selection.Delete 1

I can't see what is wrong with this. I was certain that changing Columns to Rows as you both suggested would work...

 

[JSMITH242B]

It now works!! Thank you!!

Funny thing is the deleteColumns procedure is EXACTLY the same as how I had it so is the calling statement.

I deleted some code that incremented the column count - didn't think it was necessary. Could this have been the 'red herring'? (Can't remember what the code was!!)

cheers
J