In Excel "cannot use command on overlapping selections"

rudolphrc

New Member
Joined
Mar 17, 2013
Messages
24
Folks,

I attempted to record a macro on the steps required to clean some data received from another person. I save the macro run it in another workbook and it stops.

I was simply trying to delete the first 3 rows then delete all blank columns and all blank rows. I've noticed in the data that Columns C&D are merged & center. I've deleted the column D and to no avail I still have the run-time error '1004' cannot use command on overlapping selections

I'm trying to use the data to do Pivot Tables. What code can i use to fix below? I've changed some the data to protect the innocent.

1588225441210.png
1588225782422.png
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,509
Office Version
  1. 2013
Platform
  1. Windows
Might be easier to post your code, but it looks like you are trying to Select a column....then delete a row !!
 

rudolphrc

New Member
Joined
Mar 17, 2013
Messages
24
Might be easier to post your code, but it looks like you are trying to Select a column....then delete a row !!

That is exactly what I'm trying to do. I go to Column that I no has blank spaces because of trying to remove the date in those cell that are to the left of that column.

I want to delete columns b, j, l, n, p, r, t, v, x, z, regardless if there is text in the cells.


Rich (BB code):
Sub bldg()
'
' bldg Macro
' Formatting data to support Pivot Table
'
' Keyboard Shortcut: Ctrl+j
'
    Rows("1:3").Select
    Selection.Delete Shift:=xlUp
    Columns("AA:AA").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.EntireRow.Delete
    ActiveWindow.SmallScroll Down:=-3
    ActiveWindow.SmallScroll Down:=-6
    ActiveWindow.SmallScroll Down:=-9
    Range("A1:AA1").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    ActiveWindow.SmallScroll Down:=3
    ActiveWindow.SmallScroll Down:=6
    ActiveWindow.SmallScroll Down:=9
    ActiveWindow.SmallScroll Down:=12
    ActiveWindow.SmallScroll Down:=15
    ActiveWindow.SmallScroll Down:=18
    ActiveWindow.SmallScroll Down:=21
    ActiveWindow.SmallScroll Down:=18
    ActiveWindow.SmallScroll Down:=15
    ActiveWindow.SmallScroll Down:=12
    ActiveWindow.SmallScroll Down:=9
    ActiveWindow.SmallScroll Down:=6
    ActiveWindow.SmallScroll Down:=3
    ActiveWindow.SmallScroll Down:=0
    ActiveWindow.SmallScroll Down:=-3
    ActiveWindow.SmallScroll Down:=-6
    ActiveWindow.SmallScroll Down:=-9
    ActiveWindow.SmallScroll Down:=-12
    ActiveWindow.SmallScroll Down:=-15
    ActiveWindow.SmallScroll Down:=-18
    ActiveWindow.SmallScroll Down:=-21
    ActiveWindow.SmallScroll Down:=-24
    ActiveWindow.SmallScroll Down:=-27
    ActiveWindow.SmallScroll Down:=-30
    ActiveWindow.SmallScroll Down:=-27
    ActiveWindow.SmallScroll Down:=-24
    ActiveWindow.SmallScroll Down:=-21
    ActiveWindow.SmallScroll Down:=-18
    ActiveWindow.SmallScroll Down:=-21
    ActiveWindow.SmallScroll Down:=-18
    ActiveWindow.SmallScroll Down:=-15
    ActiveWindow.SmallScroll Down:=-12
    ActiveWindow.SmallScroll Down:=-15
    ActiveWindow.SmallScroll Down:=-18
    ActiveWindow.SmallScroll Down:=-21
    ActiveWindow.SmallScroll Down:=-24
    ActiveWindow.SmallScroll Down:=-27
    ActiveWindow.SmallScroll Down:=-30
    ActiveWindow.SmallScroll Down:=-33
    ActiveWindow.SmallScroll Down:=-36
    ActiveWindow.SmallScroll Down:=-39
    ActiveWindow.SmallScroll Down:=-42
    ActiveWindow.SmallScroll Down:=-45
    ActiveWindow.SmallScroll Down:=-48
    ActiveWindow.SmallScroll Down:=-51
    ActiveWindow.SmallScroll Down:=-54
    ActiveWindow.SmallScroll Down:=-6
    Columns("D:D").Select
    Range("D98").Activate
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveWindow.SmallScroll Down:=-90
    Columns("D:D").EntireColumn.AutoFit
    Columns("D:D").Select
    Range("D98").Activate
    Selection.Delete Shift:=xlToLeft
    ActiveWindow.ScrollRow = 80
    ActiveWindow.ScrollRow = 79
    ActiveWindow.ScrollRow = 77
    ActiveWindow.ScrollRow = 75
    ActiveWindow.ScrollRow = 71
    ActiveWindow.ScrollRow = 57
    ActiveWindow.ScrollRow = 53
    ActiveWindow.ScrollRow = 40
    ActiveWindow.ScrollRow = 30
    ActiveWindow.ScrollRow = 24
    ActiveWindow.ScrollRow = 22
    ActiveWindow.ScrollRow = 16
    ActiveWindow.ScrollRow = 14
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 1
    Columns("D:D").Select
    Range("D98").Activate
    Selection.Delete Shift:=xlToLeft
    Columns("C:C").EntireColumn.AutoFit
    ActiveWindow.SmallScroll Down:=-12
    ActiveWindow.ScrollRow = 68
    ActiveWindow.ScrollRow = 67
    ActiveWindow.ScrollRow = 65
    ActiveWindow.ScrollRow = 60
    ActiveWindow.ScrollRow = 58
    ActiveWindow.ScrollRow = 56
    ActiveWindow.ScrollRow = 54
    ActiveWindow.ScrollRow = 47
    ActiveWindow.ScrollRow = 41
    ActiveWindow.ScrollRow = 35
    ActiveWindow.ScrollRow = 33
    ActiveWindow.ScrollRow = 27
    ActiveWindow.ScrollRow = 25
    ActiveWindow.ScrollRow = 21
    ActiveWindow.ScrollRow = 11
    ActiveWindow.ScrollRow = 8
    ActiveWindow.SmallScroll Down:=-6
    ActiveWindow.SmallScroll ToRight:=1
    ActiveWindow.SmallScroll Down:=-18
    ActiveWindow.ScrollColumn = 1
    ActiveWindow.SmallScroll Down:=-9
    Range("A1:Z1").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.EntireColumn.Delete
End Sub
 
Last edited by a moderator:

Watch MrExcel Video

Forum statistics

Threads
1,119,099
Messages
5,576,127
Members
412,698
Latest member
Lilly13m
Top