Fun with Overlapping Selections

Happy2Learn

Board Regular
Joined
Jul 28, 2004
Messages
68
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!
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Public Sub DeleteBlankRows()
Sheets("temp").Select
Range("A4:A" & Range("A65536").End(xlUp).Row).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Range("A3").Select
End Sub
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top