Error 1004 - Unable to set specialcells property

D

Darach

I am creating a macro enabled spreadsheet using Excel 2003. It reads data in
from a .csv file and manipulates this data to create several reports.

Some of the report users use Excel 97, and get the run time error 'Error
1004 - Unable to set specialcells property' when they try to run the macro
that updates the reports from the .csv.

I am using the special cells property to find the range that I need to clear
when reading in new data.

Does anyone know what might be causing this problem?

Thanks in advance
 
D

Darach

Thanks for the reply - the sub routine that produces the error is below.
Passed into the routine are:

lonRow - first row to clear
strCol - last column to clear
wkSheet - worksheet to clear

code:

Sub ClearList(lonRow As Long, strCol As String, wkSheet As Worksheet)
Dim rngClear As Range
Dim rngLast As Range
Dim strRange As String

'get last row
Set rngLast = wkSheet.Range("A" & lonRow).SpecialCells(xlCellTypeLastCell)
lLastrow = rngLast.Row

'create range string
strRange = "A" & lonRow & ":" & strCol & (lLastrow + 1)

Set rngClear = wkSheet.Range(strRange)
rngClear.Delete

End Sub
 
E

evil man

absolutely shi
like....................................................................................................................................................................................................................................................................................................EXCEL!!!!
 
F

Frank Kabel

Hi
you only apply specialcells on a single cell. Do you mean
Set rngLast = wkSheet.Range("A1:A" &
lonRow).SpecialCells(xlCellTypeLastCell)
 
D

Darach

Yes, this is essentially what it does using the specialcells
xlCellTypeLastCell to create a range. The lonRow parameter is used in case I
want to clear an area below the first row.

Anyway, I found the solution. In Excel 97 I needed to activate the worksheet
in code (wkSheet.Activate), this wasn't necessary in Excel 2003 - don't know
why.
 
Top