Error - Cannot Use that command on overlapping sections. SpecialCells(xlCellTypeBlanks) 
Author Message
 Error - Cannot Use that command on overlapping sections. SpecialCells(xlCellTypeBlanks)

Hi All
I am using the code below to delete blank rows in a sheet. The sheet
will always have row1 containing headers, row 2 is blank and the data
starts at row3. I found that my code works fine when the number of
rows is greater than 3, but if its 3 (i.e. a header, plus a blank row
plus one row of data) then I get the error message

"Cannot Use that command on overlapping sections".

I can add a check for the row qty, but I would like to understand what
is happening.

My range for checking for blanks is all cells in column "AY" up to the
range of the sheet, starting from cell "AY3".

Note that sheetout(x) is a sheet as part of an array that I already
set up.

Dim RngBlanks as range
Dim PECColumn as Range
Set PECColumn = sheetout(x).Range("AY3:AY" &
sheetout(x).UsedRange.Rows.Count)

On Error Resume Next
Set RngBlanks = PECColumn.SpecialCells(xlCellTypeBlanks)
 On Error GoTo 0 'Turn off Current Error Trap i.e. Resume Next
        If Not RngBlanks Is Nothing Then
            RngBlanks.EntireRow.Delete  '<----Error occurs here
        End If

Any help greatly appreciated!

Thanks
Andrew



Fri, 16 Sep 2005 17:35:24 GMT  
 Error - Cannot Use that command on overlapping sections. SpecialCells(xlCellTypeBlanks)
Andrew,
With the code below substituting sheets(1) for sheetout(x) it seems to work
fine for me.
Dim RngBlanks As Range
Dim PECColumn As Range
Set PECColumn = Sheets(1).Range("AY3:AY" & _
Sheets(1).UsedRange.Rows.Count)

On Error Resume Next
Set RngBlanks = PECColumn.SpecialCells(xlCellTypeBlanks)
 On Error GoTo 0 'Turn off Current Error Trap i.e. Resume Next
        If Not RngBlanks Is Nothing Then
            RngBlanks.EntireRow.Delete  '<----Error occurs here
        End If


Quote:
> Hi All
> I am using the code below to delete blank rows in a sheet. The sheet
> will always have row1 containing headers, row 2 is blank and the data
> starts at row3. I found that my code works fine when the number of
> rows is greater than 3, but if its 3 (i.e. a header, plus a blank row
> plus one row of data) then I get the error message

> "Cannot Use that command on overlapping sections".

> I can add a check for the row qty, but I would like to understand what
> is happening.

> My range for checking for blanks is all cells in column "AY" up to the
> range of the sheet, starting from cell "AY3".

> Note that sheetout(x) is a sheet as part of an array that I already
> set up.

> Dim RngBlanks as range
> Dim PECColumn as Range
> Set PECColumn = sheetout(x).Range("AY3:AY" &
> sheetout(x).UsedRange.Rows.Count)

> On Error Resume Next
> Set RngBlanks = PECColumn.SpecialCells(xlCellTypeBlanks)
>  On Error GoTo 0 'Turn off Current Error Trap i.e. Resume Next
>         If Not RngBlanks Is Nothing Then
>             RngBlanks.EntireRow.Delete  '<----Error occurs here
>         End If

> Any help greatly appreciated!

> Thanks
> Andrew



Fri, 16 Sep 2005 21:44:05 GMT  
 Error - Cannot Use that command on overlapping sections. SpecialCells(xlCellTypeBlanks)
John
thanks for the response, unfortunately it does not work for me under
the conditions I described. It seems that the range being used for
blank cells gets messed up if only one cell is the range.

Your code is essentially equivalent to mine so you should be able to
reproduce the problem as per my description below.

Regards
Andrew

Quote:

> Andrew,
> With the code below substituting sheets(1) for sheetout(x) it seems to work
> fine for me.
> Dim RngBlanks As Range
> Dim PECColumn As Range
> Set PECColumn = Sheets(1).Range("AY3:AY" & _
> Sheets(1).UsedRange.Rows.Count)

> On Error Resume Next
> Set RngBlanks = PECColumn.SpecialCells(xlCellTypeBlanks)
>  On Error GoTo 0 'Turn off Current Error Trap i.e. Resume Next
>         If Not RngBlanks Is Nothing Then
>             RngBlanks.EntireRow.Delete  '<----Error occurs here
>         End If



> > Hi All
> > I am using the code below to delete blank rows in a sheet. The sheet
> > will always have row1 containing headers, row 2 is blank and the data
> > starts at row3. I found that my code works fine when the number of
> > rows is greater than 3, but if its 3 (i.e. a header, plus a blank row
> > plus one row of data) then I get the error message

> > "Cannot Use that command on overlapping sections".

> > I can add a check for the row qty, but I would like to understand what
> > is happening.

> > My range for checking for blanks is all cells in column "AY" up to the
> > range of the sheet, starting from cell "AY3".

> > Note that sheetout(x) is a sheet as part of an array that I already
> > set up.

> > Dim RngBlanks as range
> > Dim PECColumn as Range
> > Set PECColumn = sheetout(x).Range("AY3:AY" &
> > sheetout(x).UsedRange.Rows.Count)

> > On Error Resume Next
> > Set RngBlanks = PECColumn.SpecialCells(xlCellTypeBlanks)
> >  On Error GoTo 0 'Turn off Current Error Trap i.e. Resume Next
> >         If Not RngBlanks Is Nothing Then
> >             RngBlanks.EntireRow.Delete  '<----Error occurs here
> >         End If

> > Any help greatly appreciated!

> > Thanks
> > Andrew



Sat, 17 Sep 2005 10:01:04 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. using last cell in used range - SpecialCells method

2. Overlapping form sections in VGA screen

3. The using UsedRange to reset SpecialCells(xlCellTypeLastCell)

4. Using the SpecialCells Method

5. Using SpecialCells to clear constants

6. problem with using specialCells in WorkbookBeforeSave event

7. pc locks up when graphics overlap or overlap text

8. Application error, SpecialCells

9. Trapping Error generated by SpecialCells

10. SpecialCells returns Error 1004


 
Powered by phpBB® Forum Software © phpBB Group