Excel - Error sorting second sheet but not first (like format)?

[lameid]

In the code block I am hitting an error on the [red]red line[/red]. The error is... Error 13 Type mismatch.

This is part of a much wider range of code that is looping through all the sheets in a workbook. It always fails on the second sheet... I have swapped positions of sheets to verify it is not sheet specific. And in both cases it gets past the first sheet.

Values in use when that line blows up...
RS!Detail_Column = 2
RS!Has_Header_Row = True

This code is inside a With Excel.Application block.

            'Delete rows that do not have required detail data
            If Nz(RS!Detail_Column, 0) > 0 Then
                RngFix.Parent.Activate
                RngFix.Cells.Select
                .Selection.Sort Key1:=RngFix.Cells(1, RS!Detail_Column), Order1:=xlAscending, Header:=IIf(RS!Has_Header_Row, xlYes, xlNo), _
                        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                        DataOption1:=xlSortNormal
                    
                RngFix.Cells(1, RS!Detail_Column).Select
                .Selection.End(xlDown).Select
                .Rows(.Selection.Row + 1).Select
                .Range(.Selection, .Selection.End(xlDown)).Select
                .Selection.Delete Shift:=xlUp
                
                'Sort Descending in case Text needs to be at top
                RngFix.Cells.Select 'Select all
                [red].Selection.Sort Key1:=.Cells(1, RS!Detail_Column), Order1:=xlDescending, Header:=IIf(RS!Has_Header_Row, xlYes, xlNo), _
                        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                        DataOption1:=xlSortNormal[/red]
            End If

So how is the sheet specified since that seems causal?

[b].Sheets(lngSheet_Index).Select[/b]
            .Sheets(lngSheet_Index).AutoFilterMode = False 'Apparently this can cause the delete blank rows section to blow up if it exists
            strSheetName = .Sheets(lngSheet_Index).Name
            
            If Nz(RS!NamedRange, "") <> "" Then
                Set RngFix = .ActiveSheet.Range(RS!NamedRange) 'Named Range
            Else
                'Set RngFix = .ActiveSheet.Cells 'Entire Sheet
                [b]Set RngFix = .Range(.ActiveSheet.Cells(1, 1), .ActiveSheet.Cells(1, 1).SpecialCells(xlLastCell))[/b] 'Work only with data on sheet
            End If

Noteworthy is that this procedure listed here is iteratively called by an outer looping procedure that opens and saves the workbook with changes to the sheet but should have little to do with the error.

Just dumbfounded on this one so any ideas?

Even making Excel visible I don't spot anything, the appropriate range IS selected. I could even manually sort the contents in debug mode.

 

[SkipVought]

hi,

I'd try something like this

'Delete rows that do not have required detail data
    If Nz(RS!Detail_Column, 0) > 0 Then
        With RngFix.Parent  '[b]reference this sheet object[/b]
            .CurrentRegion.Sort _
                Key1:=RngFix.Cells(1, RS!Detail_Column), _
                Order1:=xlAscending, _
                Header:=IIf(RS!Has_Header_Row, xlYes, xlNo), _
                OrderCustom:=1, _
                MatchCase:=False, _
                Orientation:=xlTopToBottom, _
                DataOption1:=xlSortNormal
                
            With RngFix.Cells(1, RS!Detail_Column).End(xlDown).Offset(1)    '[b]reference this cell[/b]
                Range(.Cells, .Cells.End(xlDown)).Delete Shift:=xlUp
            End With
            
            'Sort Descending in case Text needs to be at top
            .CurrentRegion.Sort _
                Key1:=RngFix.Cells(1, RS!Detail_Column), _
                Order1:=xlDescending, _
                Header:=IIf(RS!Has_Header_Row, xlYes, xlNo), _
                OrderCustom:=1, _
                MatchCase:=False, _
                Orientation:=xlTopToBottom, _
                DataOption1:=xlSortNormal
        End With
    End If

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lameid]

CurrentRegion is not available... But looking it up, the reason to use it is that there is empty space?

BTW, this code is running from Access.

 

[SkipVought]

What does the [highlight]DOT[/highlight] reference refer to in your code?

[highlight].[/highlight]Selection.Sort Key1:=[highlight].[/highlight]Cells(1, RS!Detail_Column), Order1:=xlDescending, Header:=IIf(RS!Has_Header_Row, xlYes, xlNo), _
                        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                        DataOption1:=xlSortNormal

do you have a reference set in Tools/Options to an Excel Object Library?

If NOT, then you cannot use xlYes / xlNo.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lameid]

This code is inside a With Excel.Application block.
Yes there is an excel reference.
It runs for the first sheet that is formatted the same. Fails on Second sheet regardless or order of sheets in workbook. I can watch it see that the appropriate data is selected and bam errors out to the calling procedure when I step into that line (odd as I don't have error handlers plugged in). I have also run a /decompile on the Access file and compact repair had the issue on 2 computers both with Office 2007 and now mine with 2010 (32 bit).

This always on the second sheet phenomenon makes me question things like selections and the way RNGFIX is established but based on the bold lines in my second code window in the OP, I don't see this as the problem given everything looks good when I make Excel visible.