In Excel "cannot use command on overlapping selections"

[rudolphrc]

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.

 

[Michael M]

Might be easier to post your code, but it looks like you are trying to Select a column....then delete a row !!

 

[rudolphrc]

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.

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