Results 1 to 8 of 8

Fun with Overlapping Selections

This is a discussion on Fun with Overlapping Selections within the Excel Questions forums, part of the Question Forums category; Hi Excel'rs. My macro's started to halt with the Error 'Runtime Error 1004' Cannot use that command on overlapping selections. ...

  1. #1
    Board Regular
    Join Date
    Jul 2004
    Posts
    68

    Default Fun with Overlapping Selections

    Hi Excel'rs.

    My macro's started to halt with the Error 'Runtime Error 1004' Cannot use that command on overlapping selections. Problem is I'm not sure I am selecting overlapping selections.

    Here's the offending code.....

    Sheets("temp").Select
    Range("A4:A" & Range("A65536").End(xlUp).Row).Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.EntireRow.Delete
    Range("A3").Select

    This selects all on column A from A4 to the bottom, does a goto blanks and deletes entire selected row.
    This has worked in the past and I'm not sure what would cause this error.

    Any thoughts?

    Hope you can help!

  2. #2
    Board Regular
    Join Date
    May 2002
    Location
    Dallas
    Posts
    366

    Default

    My VBA's a little rusty, but if you took the .Select off of the Range("A4:A" & Range("A65536").End(xlUp).Row).Select statement (thereby just declaring it a range to work in), THEN .Selecting the Blank cells, you won't be overlapping.

  3. #3
    Board Regular George J's Avatar
    Join Date
    Feb 2002
    Location
    Edinburgh, Bonnie Scotland
    Posts
    844

    Default Re: Fun with Overlapping Selections

    Does it help if you make sure that the end.(xlup).row value is greater than 4?

    If there is no data in the row it will create an error
    George J

  4. #4
    Board Regular
    Join Date
    Aug 2003
    Location
    England
    Posts
    4,821

    Default Re: Fun with Overlapping Selections

    Hello,

    I have managed to recreate it by having cell A4 as the last one in that row, you may need to check for this condition.
    -------------------------
    Hope this is helpful.
    -------------------------

    Have been away from the forum for quite a while, but am now back.

  5. #5
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259

    Default Re: Fun with Overlapping Selections

    Public Sub DeleteBlankRows()
    Sheets("temp").Select
    Range("A4:A" & Range("A65536").End(xlUp).Row).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    Range("A3").Select
    End Sub

    <MARQUEE>...........Never be afraid to try something new. Remember, amateurs built the ark, professionals built the Titanic...............The easiest thing to find is fault, don't be easy !.. --Anonymous--...</marquee>

  6. #6
    Board Regular
    Join Date
    Jul 2004
    Posts
    68

    Default Re: Fun with Overlapping Selections

    Sorry I didn't back till now.

    Thanks for the suggestions. Nimrod, your change worked. The scary part for me is I'm not sure what the difference is between

    Range("A4:A" & Range("A65536").End(xlUp).Row).Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.EntireRow.Delete

    and your fix

    Range("A4:A" & Range("A65536").End(xlUp).Row).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

    If you get a moment could you explain.

    Thanks

  7. #7
    Board Regular chomsky's Avatar
    Join Date
    Mar 2009
    Posts
    178

    Default Re: Fun with Overlapping Selections

    Same is happening Here any Help ?

    Sub DeleteRowsMP()
    Dim rTable As Range
    Dim lCol As Long
    Dim vCriteria
    On Error Resume Next
    'Determine the table range
    With Selection
    If .Cells.Count > 1 Then
    Set rTable = Selection
    Else
    Set rTable = .CurrentRegion
    On Error GoTo 0
    End If
    End With 'Determine if table range is valid
    If rTable Is Nothing Or rTable.Cells.Count = 1 Or WorksheetFunction.CountA(rTable) < 2 Then
    MsgBox "No pude determinar el rango.!", vbCritical, "FUERA DE RANGO"
    Exit Sub
    End If
    'Get the criteria in the form of text or number.
    vCriteria = Application.InputBox(Prompt:="Escriba Criteria a usar para borrar Rows/Filas. " & "Si la criteria se encuentra en una de las celdas, escoja la celda con su mouse", _
    Title:="CRITERIA CONDICIONAL PARA BORRAR FILAS", Type:=1 + 2)
    'Go no further if they Cancel.
    If vCriteria = "False" Then Exit Sub
    'Get the relative column number where the criteria should be found
    lCol = Application.InputBox(Prompt:="Entre el numero relativo de la columna donde " _
    & "puede conseguir la criteria.", Title:="NUMERO DE LA COLUMNA CON LA CONDICIÓN PARA BORRAR LAS FILAS", Type:=1)
    'Cancelled
    If lCol = 0 Then Exit Sub
    'Remove any existing AutoFilters
    ActiveSheet.AutoFilterMode = False
    'Filter table based on vCriteria using the relative column position stored in lCol.
    rTable.AutoFilter Field:=lCol, Criteria1:=vCriteria
    'Delete all rows that are NOT hidden by AutoFilter.
    rTable.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    'Remove AutoFilters
    ActiveSheet.AutoFilterMode = False
    On Error GoTo 0
    End Sub

  8. #8
    New Member
    Join Date
    Mar 2009
    Posts
    36

    Default Re: Fun with Overlapping Selections

    Yes I seem to get that message when I try to delete a number of columns

    Cells.Select
    Selection.EntireColumn.Hidden = False
    Columns("A:A").Select
    Range("A:A,J:J,K:K,M:M,P:P,Q:Q,T:T,U:U,X:X,Y:Y").Select
    Range("Y2").Activate
    Selection.Delete Shift:=xlToLeft

    When I run the debugger it highlights Selection.Delete Shift:=xlToLeft in yellow.

    Any thoughts

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com