Delete coumns using macro error

C

customesp

New Member
Joined
Oct 11, 2013
Messages
34
Good Afternoon,

I am trying to delete every 2nd and 3rd column in a 3 column pattern repeating until the end, where I only want to keep the 1st column, 4th column, 7th column etc. in a spreadsheet repeating until the end.I am getting this error...which I highlighted in red. Thanks,

ub Macro14()
'
' Macro14 Macro
'
' Keyboard Shortcut: Ctrl+Shift+X
'
ActiveCell.Offset(-57, 1).Range("A1:B11,D1:E11,G1:H11,J1:K11,M1:N12,P1:Q11"). _
Select
ActiveCell.Offset(-57, 16).Range("A1").Activate
 ActiveWindow.Zoom = 50
ActiveWindow.Zoom = 40
ActiveWindow.Zoom = 30
ActiveWindow.Zoom = 40
ActiveWindow.Zoom = 50
ActiveWindow.Zoom = 60
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveCell.Offset(0, -15).Range( _
"A1:B11,D1:E11,G1:H11,J1:K11,M1:N12,P1:Q11,S1:T11,V1:W11,Y1:Z11,AB1:AC11"). _
Select
ActiveCell.Offset(0, 12).Range("A1").Activate
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 28
ActiveCell.Offset(0, -27).Range( _
"A1:B11,D1:E11,G1:H11,J1:K11,M1:N12,P1:Q11,S1:T11,V1:W11,Y1:Z11,AB1:AC11,AE1:AF11,AH1:AI11,AK1:AL11,AN1:AO11,AQ1:AR11" _
).Select
ActiveCell.Offset(0, 15).Range("A1").Activate
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 38
ActiveWindow.ScrollColumn = 39
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 41
ActiveWindow.ScrollColumn = 42
ActiveWindow.ScrollColumn = 43
ActiveWindow.ScrollColumn = 44
ActiveWindow.ScrollColumn = 45
ActiveWindow.ScrollColumn = 46
ActiveWindow.SmallScroll ToRight:=-1
ActiveCell.Offset(0, -42).Range( _
"A1:B11,D1:E11,G1:H11,J1:K11,M1:N12,P1:Q11,S1:T11,V1:W11,Y1:Z11,AB1:AC11,AE1:AF11,AH1:AI11,AK1:AL11,AN1:AO11,AQ1:AR11,AT1:AU11,AW1:AX11,AZ1:BA11,BC1:BD11,BF1:BG11" _
).Select
ActiveCell.Offset(0, 15).Range("A1").Activate
ActiveWindow.ScrollColumn = 46
ActiveWindow.ScrollColumn = 47
ActiveWindow.ScrollColumn = 48
ActiveWindow.ScrollColumn = 49
ActiveWindow.ScrollColumn = 50
ActiveWindow.ScrollColumn = 51
ActiveWindow.ScrollColumn = 52
ActiveWindow.ScrollColumn = 53
ActiveWindow.ScrollColumn = 54
ActiveWindow.ScrollColumn = 55
ActiveWindow.ScrollColumn = 56
ActiveWindow.ScrollColumn = 57
ActiveWindow.ScrollColumn = 58
ActiveWindow.ScrollColumn = 59
ActiveWindow.ScrollColumn = 60
ActiveWindow.SmallScroll ToRight:=-2
ActiveCell.Offset(0, -57).Range( _
"A1:B11,D1:E11,G1:H11,J1:K11,M1:N12,P1:Q11,S1:T11,V1:W11,Y1:Z11,AB1:AC11,AE1:AF11,AH1:AI11,AK1:AL11,AN1:AO11,AQ1:AR11,AT1:AU11,AW1:AX11,AZ1:BA11,BC1:BD11,BF1:BG11,BI1:BJ11,BL1:BM11,BO1:BP11,BR1:BS11,BU1:BV11" _
).Select
ActiveCell.Offset(0, 15).Range("A1").Activate
ActiveWindow.ScrollColumn = 59
ActiveWindow.ScrollColumn = 60
ActiveWindow.ScrollColumn = 61
ActiveWindow.ScrollColumn = 62
ActiveWindow.ScrollColumn = 63
ActiveWindow.ScrollColumn = 64
ActiveWindow.ScrollColumn = 65
ActiveWindow.ScrollColumn = 66
ActiveWindow.ScrollColumn = 67
ActiveCell.Offset(0, -72).Range( _
"A1:B11,D1:E11,G1:H11,J1:K11,M1:N12,P1:Q11,S1:T11,V1:W11,Y1:Z11,AB1:AC11,AE1:AF11,AH1:AI11,AK1:AL11,AN1:AO11,AQ1:AR11,AT1:AU11,AW1:AX11,AZ1:BA11,BC1:BD11,BF1:BG11,BI1:BJ11,BL1:BM11,BO1:BP11,BR1:BS11,BU1:BV11,BX1:BY11,CA1:CB11,CD1:CE11" _
).Select
ActiveCell.Offset(0, 9).Range("A1").Activate
ActiveWindow.ScrollColumn = 68
ActiveWindow.ScrollColumn = 69
ActiveWindow.ScrollColumn = 70
ActiveWindow.ScrollColumn = 71
ActiveWindow.ScrollColumn = 72
ActiveWindow.ScrollColumn = 73
ActiveWindow.ScrollColumn = 74
ActiveWindow.ScrollColumn = 75
ActiveWindow.ScrollColumn = 76
ActiveWindow.ScrollColumn = 77
ActiveWindow.ScrollColumn = 78
ActiveWindow.ScrollColumn = 79
ActiveWindow.ScrollColumn = 80
ActiveWindow.ScrollColumn = 81
ActiveWindow.ScrollColumn = 82
ActiveWindow.ScrollColumn = 83
ActiveWindow.ScrollColumn = 84
ActiveWindow.ScrollColumn = 83
ActiveWindow.ScrollColumn = 82
ActiveCell.Offset(0, -81).Union(Range( _
"CS1:CT11,A1:B11,D1:E11,G1:H11,J1:K11,M1:N12,P1:Q11,S1:T11,V1:W11,Y1:Z11,AB1:AC11,AE1:AF11,AH1:AI11,AK1:AL11,AN1:AO11,AQ1:AR11,AT1:AU11,AW1:AX11,AZ1:BA11,BC1:BD11,BF1:BG11,BI1:BJ11,BL1:BM11,BO1:BP11,BR1:BS11,BU1:BV11,BX1:BY11,CA1:CB11,CD1:CE11,CG1:CH11" _
), Range("CP1:CQ11")).Select
ActiveCell.Offset(0, 15).Range("A1").Activate
ActiveWindow.ScrollColumn = 83
ActiveWindow.ScrollColumn = 84
ActiveWindow.ScrollColumn = 85
ActiveWindow.ScrollColumn = 86
ActiveWindow.ScrollColumn = 87
ActiveWindow.ScrollColumn = 88
ActiveWindow.ScrollColumn = 89
ActiveWindow.ScrollColumn = 90
ActiveWindow.ScrollColumn = 91
ActiveWindow.ScrollColumn = 92
ActiveWindow.ScrollColumn = 93
ActiveWindow.ScrollColumn = 94
ActiveWindow.ScrollColumn = 95
ActiveWindow.ScrollColumn = 96
ActiveWindow.ScrollColumn = 97
ActiveWindow.ScrollColumn = 95
ActiveCell.Offset(0, -96).Union(Range( _
"CS1:CT11,CV1:CW11,CY1:CZ11,DB1:DC11,DE1:DF11,A1:B11,D1:E11,G1:H11,J1:K11,M1:N12,P1:Q11,S1:T11,V1:W11,Y1:Z11,AB1:AC11,AE1:AF11,AH1:AI11,AK1:AL11,AN1:AO11,AQ1:AR11,AT1:AU11,AW1:AX11,AZ1:BA11,BC1:BD11,BF1:BG11,BI1:BJ11,BL1:BM11,BO1:BP11,BR1:BS11,BU1:BV11" _
), Range("CD1:CE11,CG1:CH11,CJ1:CK11,CM1:CN11,CP1:CQ11")).Select
ActiveCell.Offset(0, 12).Range("A1").Activate
ActiveWindow.ScrollColumn = 96
ActiveWindow.ScrollColumn = 97
ActiveWindow.ScrollColumn = 98
ActiveWindow.ScrollColumn = 99
ActiveWindow.ScrollColumn = 100
ActiveWindow.ScrollColumn = 101
ActiveWindow.ScrollColumn = 102
ActiveWindow.ScrollColumn = 103
ActiveWindow.ScrollColumn = 104
ActiveWindow.ScrollColumn = 105
ActiveWindow.ScrollColumn = 106
ActiveCell.Offset(0, -108).Union(Range( _
"CS1:CT11,CV1:CW11,CY1:CZ11,DB1:DC11,DE1:DF11,DH1:DI11,DK1:DL11,DN1:DO11,DQ1:DR11,A1:B11,D1:E11,G1:H11,J1:K11,M1:N12,P1:Q11,S1:T11,V1:W11,Y1:Z11,AB1:AC11,AE1:AF11,AH1:AI11,AK1:AL11,AN1:AO11,AQ1:AR11,AT1:AU11,AW1:AX11,AZ1:BA11,BC1:BD11,BF1:BG11,BI1:BJ11" _
), Range( _
"BR1:BS11,BU1:BV11,BX1:BY11,CA1:CB11,CD1:CE11,CG1:CH11,CJ1:CK11,CM1:CN11,CP1:CQ11" _
)).Select
ActiveCell.Offset(0, 12).Range("A1").Activate
ActiveWindow.ScrollColumn = 107
ActiveWindow.ScrollColumn = 108
ActiveWindow.ScrollColumn = 109
ActiveWindow.ScrollColumn = 110
ActiveWindow.ScrollColumn = 111
ActiveWindow.ScrollColumn = 112
ActiveWindow.ScrollColumn = 113
ActiveWindow.ScrollColumn = 114
ActiveWindow.ScrollColumn = 115
ActiveWindow.ScrollColumn = 116
ActiveWindow.ScrollColumn = 117
ActiveWindow.ScrollColumn = 118
ActiveWindow.ScrollColumn = 119
ActiveWindow.ScrollColumn = 120
ActiveWindow.ScrollColumn = 121
ActiveWindow.ScrollColumn = 122
ActiveCell.Offset(0, -120).Union(Range( _
"CS1:CT11,CV1:CW11,CY1:CZ11,DB1:DC11,DE1:DF11,DH1:DI11,DK1:DL11,DN1:DO11,DQ1:DR11,DT1:DU11,DW1:DX11,DZ1:EA11,EC1:ED11,EF1:EG11,A1:B11,D1:E11,G1:H11,J1:K11,M1:N12,P1:Q11,S1:T11,V1:W11,Y1:Z11,AB1:AC11,AE1:AF11,AH1:AI11,AK1:AL11,AN1:AO11,AQ1:AR11,AT1:AU11" _
), Range( _
"BC1:BD11,BF1:BG11,BI1:BJ11,BL1:BM11,BO1:BP11,BR1:BS11,BU1:BV11,BX1:BY11,CA1:CB11,CD1:CE11,CG1:CH11,CJ1:CK11,CM1:CN11,CP1:CQ11" _
)).Select
ActiveCell.Offset(0, 15).Range("A1").Activate
ActiveWindow.ScrollColumn = 123
ActiveWindow.ScrollColumn = 124
ActiveWindow.ScrollColumn = 125
ActiveWindow.ScrollColumn = 126
ActiveWindow.ScrollColumn = 127
ActiveWindow.ScrollColumn = 128
ActiveWindow.ScrollColumn = 129
ActiveWindow.ScrollColumn = 130
ActiveWindow.ScrollColumn = 131
ActiveWindow.ScrollColumn = 132
ActiveWindow.ScrollColumn = 133
ActiveWindow.ScrollColumn = 134
ActiveWindow.ScrollColumn = 135
ActiveCell.Offset(0, -135).Union(Range( _
"CS1:CT11,CV1:CW11,CY1:CZ11,DB1:DC11,DE1:DF11,DH1:DI11,DK1:DL11,DN1:DO11,DQ1:DR11,DT1:DU11,DW1:DX11,DZ1:EA11,EC1:ED11,EF1:EG11,EI1:EJ11,EL1:EM11,EO1:EP11,ER1:ES11,EU1:EV11,A1:B11,D1:E11,G1:H11,J1:K11,M1:N12,P1:Q11,S1:T11,V1:W11,Y1:Z11,AB1:AC11,AE1:AF11" _
), Range( _
"AN1:AO11,AQ1:AR11,AT1:AU11,AW1:AX11,AZ1:BA11,BC1:BD11,BF1:BG11,BI1:BJ11,BL1:BM11,BO1:BP11,BR1:BS11,BU1:BV11,BX1:BY11,CA1:CB11,CD1:CE11,CG1:CH11,CJ1:CK11,CM1:CN11,CP1:CQ11" _
)).Select
ActiveCell.Offset(0, 15).Range("A1").Activate
ActiveWindow.ScrollColumn = 136
ActiveWindow.ScrollColumn = 137
ActiveWindow.ScrollColumn = 138
ActiveWindow.ScrollColumn = 139
ActiveWindow.ScrollColumn = 140
ActiveWindow.ScrollColumn = 141
ActiveWindow.ScrollColumn = 142
ActiveWindow.ScrollColumn = 143
ActiveWindow.ScrollColumn = 144
ActiveWindow.ScrollColumn = 145
ActiveWindow.ScrollColumn = 146
ActiveWindow.ScrollColumn = 147
ActiveWindow.ScrollColumn = 148
ActiveWindow.ScrollColumn = 149
ActiveWindow.ScrollColumn = 150
ActiveCell.Offset(0, -150).Union(Range( _
"CS1:CT11,CV1:CW11,CY1:CZ11,DB1:DC11,DE1:DF11,DH1:DI11,DK1:DL11,DN1:DO11,DQ1:DR11,DT1:DU11,DW1:DX11,DZ1:EA11,EC1:ED11,EF1:EG11,EI1:EJ11,EL1:EM11,EO1:EP11,ER1:ES11,EU1:EV11,EX1:EY11,FA1:FB11,FD1:FE11,FG1:FH11,A1:B11,D1:E11,G1:H11,J1:K11,M1:N12,P1:Q11,S1:T11" _
), Range( _
"AB1:AC11,AE1:AF11,AH1:AI11,AK1:AL11,AN1:AO11,AQ1:AR11,AT1:AU11,AW1:AX11,AZ1:BA11,BC1:BD11,BF1:BG11,BI1:BJ11,BL1:BM11,BO1:BP11,BR1:BS11,BU1:BV11,BX1:BY11,CA1:CB11,CD1:CE11,CG1:CH11,CJ1:CK11,CM1:CN11,CP1:CQ11" _
)).Select
ActiveCell.Offset(0, 12).Range("A1").Activate
ActiveWindow.ScrollColumn = 151
ActiveWindow.ScrollColumn = 152
ActiveWindow.ScrollColumn = 153
ActiveWindow.ScrollColumn = 154
ActiveWindow.ScrollColumn = 155
ActiveWindow.ScrollColumn = 156
ActiveWindow.ScrollColumn = 157
ActiveWindow.ScrollColumn = 158
ActiveWindow.ScrollColumn = 159
ActiveWindow.ScrollColumn = 160
ActiveWindow.ScrollColumn = 161
ActiveWindow.ScrollColumn = 162
ActiveWindow.ScrollColumn = 163
ActiveWindow.ScrollColumn = 164
ActiveCell.Offset(0, -162).Union(Range( _
"CS1:CT11,CV1:CW11,CY1:CZ11,DB1:DC11,DE1:DF11,DH1:DI11,DK1:DL11,DN1:DO11,DQ1:DR11,DT1:DU11,DW1:DX11,DZ1:EA11,EC1:ED11,EF1:EG11,EI1:EJ11,EL1:EM11,EO1:EP11,ER1:ES11,EU1:EV11,EX1:EY11,FA1:FB11,FD1:FE11,FG1:FH11,FJ1:FK11,FM1:FN11,FP1:FQ11,FS1:FT11,FV1:FW11" _
), Range( _
"M1:N12,P1:Q11,S1:T11,V1:W11,Y1:Z11,AB1:AC11,AE1:AF11,AH1:AI11,AK1:AL11,AN1:AO11,AQ1:AR11,AT1:AU11,AW1:AX11,AZ1:BA11,BC1:BD11,BF1:BG11,BI1:BJ11,BL1:BM11,BO1:BP11,BR1:BS11,BU1:BV11,BX1:BY11,CA1:CB11,CD1:CE11,CG1:CH11,CJ1:CK11,CM1:CN11,CP1:CQ11" _
)).Select
ActiveCell.Offset(0, 15).Range("A1").Activate
ActiveWindow.ScrollColumn = 165
ActiveWindow.ScrollColumn = 166
ActiveWindow.ScrollColumn = 167
ActiveWindow.ScrollColumn = 168
ActiveWindow.ScrollColumn = 169
ActiveWindow.ScrollColumn = 170
ActiveWindow.ScrollColumn = 171
ActiveWindow.ScrollColumn = 172
ActiveWindow.ScrollColumn = 173
ActiveWindow.ScrollColumn = 174
ActiveWindow.ScrollColumn = 175
ActiveCell.Offset(0, -177).Union(Range( _
"CS1:CT11,CV1:CW11,CY1:CZ11,DB1:DC11,DE1:DF11,DH1:DI11,DK1:DL11,DN1:DO11,DQ1:DR11,DT1:DU11,DW1:DX11,DZ1:EA11,EC1:ED11,EF1:EG11,EI1:EJ11,EL1:EM11,EO1:EP11,ER1:ES11,EU1:EV11,EX1:EY11,FA1:FB11,FD1:FE11,FG1:FH11,FJ1:FK11,FM1:FN11,FP1:FQ11,FS1:FT11,FV1:FW11" _
), Range( _
"A1:B11,D1:E11,G1:H11,J1:K11,M1:N12,P1:Q11,S1:T11,V1:W11,Y1:Z11,AB1:AC11,AE1:AF11,AH1:AI11,AK1:AL11,AN1:AO11,AQ1:AR11,AT1:AU11,AW1:AX11,AZ1:BA11,BC1:BD11,BF1:BG11,BI1:BJ11,BL1:BM11,BO1:BP11,BR1:BS11,BU1:BV11,BX1:BY11,CA1:CB11,CD1:CE11,CG1:CH11,CJ1:CK11" _
)).Select
ActiveCell.Offset(0, 12).Range("A1").Activate
ActiveWindow.ScrollColumn = 176
ActiveWindow.ScrollColumn = 177
ActiveWindow.ScrollColumn = 178
ActiveWindow.ScrollColumn = 179
ActiveWindow.ScrollColumn = 180
ActiveWindow.ScrollColumn = 181
ActiveWindow.ScrollColumn = 182
ActiveWindow.ScrollColumn = 183
ActiveWindow.ScrollColumn = 184
ActiveWindow.ScrollColumn = 185
ActiveWindow.ScrollColumn = 186
ActiveWindow.ScrollColumn = 187
ActiveWindow.ScrollColumn = 188
ActiveWindow.ScrollColumn = 189
ActiveCell.Offset(0, -189).Union(Range( _
"CS1:CT11,CV1:CW11,CY1:CZ11,DB1:DC11,DE1:DF11,DH1:DI11,DK1:DL11,DN1:DO11,DQ1:DR11,DT1:DU11,DW1:DX11,DZ1:EA11,EC1:ED11,EF1:EG11,EI1:EJ11,EL1:EM11,EO1:EP11,ER1:ES11,EU1:EV11,EX1:EY11,FA1:FB11,FD1:FE11,FG1:FH11,FJ1:FK11,FM1:FN11,FP1:FQ11,FS1:FT11,FV1:FW11" _
), Range( _
"GK1:GL11,GN1:GO11,GQ1:GR11,A1:B11,D1:E11,G1:H11,J1:K11,M1:N12,P1:Q11,S1:T11,V1:W11,Y1:Z11,AB1:AC11,AE1:AF11,AH1:AI11,AK1:AL11,AN1:AO11,AQ1:AR11,AT1:AU11,AW1:AX11,AZ1:BA11,BC1:BD11,BF1:BG11,BI1:BJ11,BL1:BM11,BO1:BP11,BR1:BS11,BU1:BV11,BX1:BY11,CA1:CB11" _
), Range("CJ1:CK11,CM1:CN11,CP1:CQ11")).Select
ActiveCell.Offset(0, 9).Range("A1").Activate
ActiveWindow.ScrollColumn = 190
ActiveWindow.ScrollColumn = 191
ActiveWindow.ScrollColumn = 192
ActiveWindow.ScrollColumn = 193
ActiveWindow.ScrollColumn = 194
ActiveWindow.ScrollColumn = 195
ActiveWindow.ScrollColumn = 196
ActiveWindow.ScrollColumn = 197
ActiveWindow.ScrollColumn = 198
ActiveWindow.ScrollColumn = 199
ActiveWindow.ScrollColumn = 200
ActiveWindow.ScrollColumn = 201
ActiveWindow.ScrollColumn = 202
ActiveCell.Offset(0, -198).Union(Range( _
"CS1:CT11,CV1:CW11,CY1:CZ11,DB1:DC11,DE1:DF11,DH1:DI11,DK1:DL11,DN1:DO11,DQ1:DR11,DT1:DU11,DW1:DX11,DZ1:EA11,EC1:ED11,EF1:EG11,EI1:EJ11,EL1:EM11,EO1:EP11,ER1:ES11,EU1:EV11,EX1:EY11,FA1:FB11,FD1:FE11,FG1:FH11,FJ1:FK11,FM1:FN11,FP1:FQ11,FS1:FT11,FV1:FW11" _
), Range( _
"GK1:GL11,GN1:GO11,GQ1:GR11,GT1:GU11,GW1:GX11,GZ1:HA11,HC1:HD11,HF1:HG11,HI1:HJ11,A1:B11,D1:E11,G1:H11,J1:K11,M1:N12,P1:Q11,S1:T11,V1:W11,Y1:Z11,AB1:AC11,AE1:AF11,AH1:AI11,AK1:AL11,AN1:AO11,AQ1:AR11,AT1:AU11,AW1:AX11,AZ1:BA11,BC1:BD11,BF1:BG11,BI1:BJ11" _
), Range( _
"BR1:BS11,BU1:BV11,BX1:BY11,CA1:CB11,CD1:CE11,CG1:CH11,CJ1:CK11,CM1:CN11,CP1:CQ11" _
)).Select
ActiveCell.Offset(0, 18).Range("A1").Activate
ActiveWindow.ScrollColumn = 203
ActiveWindow.ScrollColumn = 204
ActiveWindow.ScrollColumn = 205
ActiveWindow.ScrollColumn = 206
ActiveWindow.ScrollColumn = 207
ActiveWindow.ScrollColumn = 208
ActiveWindow.ScrollColumn = 209
ActiveWindow.ScrollColumn = 210
ActiveCell.Offset(0, -216).Union(Range( _
"CS1:CT11,CV1:CW11,CY1:CZ11,DB1:DC11,DE1:DF11,DH1:DI11,DK1:DL11,DN1:DO11,DQ1:DR11,DT1:DU11,DW1:DX11,DZ1:EA11,EC1:ED11,EF1:EG11,EI1:EJ11,EL1:EM11,EO1:EP11,ER1:ES11,EU1:EV11,EX1:EY11,FA1:FB11,FD1:FE11,FG1:FH11,FJ1:FK11,FM1:FN11,FP1:FQ11,FS1:FT11,FV1:FW11" _
), Range( _
"GK1:GL11,GN1:GO11,GQ1:GR11,GT1:GU11,GW1:GX11,GZ1:HA11,HC1:HD11,HF1:HG11,HI1:HJ11,HL1:HM11,A1:B11,D1:E11,G1:H11,J1:K11,M1:N12,P1:Q11,S1:T11,V1:W11,Y1:Z11,AB1:AC11,AE1:AF11,AH1:AI11,AK1:AL11,AN1:AO11,AQ1:AR11,AT1:AU11,AW1:AX11,AZ1:BA11,BC1:BD11,BF1:BG11" _
), Range( _
"BO1:BP11,BR1:BS11,BU1:BV11,BX1:BY11,CA1:CB11,CD1:CE11,CG1:CH11,CJ1:CK11,CM1:CN11,CP1:CQ11" _
)).Select
ActiveCell.Offset(0, 3).Range("A1").Activate
Selection.Delete Shift:=xlToLeft
ActiveWindow.ScrollColumn = 209
ActiveWindow.ScrollColumn = 208
ActiveWindow.ScrollColumn = 206
ActiveWindow.ScrollColumn = 204
ActiveWindow.ScrollColumn = 201
ActiveWindow.ScrollColumn = 197
ActiveWindow.ScrollColumn = 192
ActiveWindow.ScrollColumn = 188
ActiveWindow.ScrollColumn = 183
ActiveWindow.ScrollColumn = 177
ActiveWindow.ScrollColumn = 173
ActiveWindow.ScrollColumn = 168
ActiveWindow.ScrollColumn = 164
ActiveWindow.ScrollColumn = 159
ActiveWindow.ScrollColumn = 156
ActiveWindow.ScrollColumn = 153
ActiveWindow.ScrollColumn = 151
ActiveWindow.ScrollColumn = 150
ActiveWindow.ScrollColumn = 149
ActiveWindow.ScrollColumn = 148
ActiveWindow.ScrollColumn = 147
ActiveWindow.ScrollColumn = 146
ActiveWindow.ScrollColumn = 145
ActiveWindow.ScrollColumn = 144
ActiveWindow.ScrollColumn = 143
ActiveWindow.ScrollColumn = 142
ActiveWindow.ScrollColumn = 141
ActiveWindow.ScrollColumn = 140
ActiveWindow.ScrollColumn = 139
ActiveWindow.ScrollColumn = 138
ActiveWindow.ScrollColumn = 137
ActiveWindow.ScrollColumn = 136
ActiveWindow.ScrollColumn = 135
ActiveWindow.ScrollColumn = 134
ActiveWindow.ScrollColumn = 133
ActiveWindow.ScrollColumn = 132
ActiveWindow.ScrollColumn = 131
ActiveWindow.ScrollColumn = 130
ActiveWindow.ScrollColumn = 129
ActiveWindow.ScrollColumn = 128
ActiveWindow.ScrollColumn = 127
ActiveWindow.ScrollColumn = 125
ActiveWindow.ScrollColumn = 124
ActiveWindow.ScrollColumn = 122
ActiveWindow.ScrollColumn = 121
ActiveWindow.ScrollColumn = 119
ActiveWindow.ScrollColumn = 118
ActiveWindow.ScrollColumn = 116
ActiveWindow.ScrollColumn = 114
ActiveWindow.ScrollColumn = 113
ActiveWindow.ScrollColumn = 111
ActiveWindow.ScrollColumn = 109
ActiveWindow.ScrollColumn = 108
ActiveWindow.ScrollColumn = 107
ActiveWindow.ScrollColumn = 106
ActiveWindow.ScrollColumn = 105
ActiveWindow.ScrollColumn = 104
ActiveWindow.ScrollColumn = 102
ActiveWindow.ScrollColumn = 101
ActiveWindow.ScrollColumn = 100
ActiveWindow.ScrollColumn = 99
ActiveWindow.ScrollColumn = 98
ActiveWindow.ScrollColumn = 97
ActiveWindow.ScrollColumn = 96
ActiveWindow.ScrollColumn = 95
ActiveWindow.ScrollColumn = 94
ActiveWindow.ScrollColumn = 93
ActiveWindow.ScrollColumn = 92
ActiveWindow.ScrollColumn = 91
ActiveWindow.ScrollColumn = 90
ActiveWindow.ScrollColumn = 89
ActiveWindow.ScrollColumn = 88
ActiveWindow.ScrollColumn = 86
ActiveWindow.ScrollColumn = 85
ActiveWindow.ScrollColumn = 84
ActiveWindow.ScrollColumn = 83
ActiveWindow.ScrollColumn = 82
ActiveWindow.ScrollColumn = 81
ActiveWindow.ScrollColumn = 80
ActiveWindow.ScrollColumn = 79
ActiveWindow.ScrollColumn = 78
ActiveWindow.ScrollColumn = 77
ActiveWindow.ScrollColumn = 76
ActiveWindow.ScrollColumn = 75
ActiveWindow.ScrollColumn = 74
ActiveWindow.ScrollColumn = 73
ActiveWindow.ScrollColumn = 72
ActiveWindow.ScrollColumn = 71
ActiveWindow.ScrollColumn = 70
ActiveWindow.ScrollColumn = 69
ActiveWindow.ScrollColumn = 68
ActiveWindow.ScrollColumn = 67
ActiveWindow.ScrollColumn = 66
ActiveWindow.ScrollColumn = 65
ActiveWindow.ScrollColumn = 64
ActiveWindow.ScrollColumn = 63
ActiveWindow.ScrollColumn = 62
ActiveWindow.ScrollColumn = 61
ActiveWindow.ScrollColumn = 60
End Sub
 
Sort by date Sort by votes
The following code figures out how many columns you are dealing with by looking at the first row. You can change this if you need to.
But, this code should accomplish your goal.
Be sure to make a copy of your workbook and test this out on the copy because you can't undo this stuff.
Code: 
Sub DelThree()
Dim cLong As Long
cLong = Range(Cells(1, Columns.Count).Address).End(xlToLeft).Column()
For i = cLong To 1 Step -1
    If (i - 1) Mod 3 <> 0 Then
        Columns(i).EntireColumn.Delete
    End If
Next i
End Sub
 
Upvote 0
lrobbo314 said:
The following code figures out how many columns you are dealing with by looking at the first row. You can change this if you need to.
But, this code should accomplish your goal.
Be sure to make a copy of your workbook and test this out on the copy because you can't undo this stuff.
Code: 
Sub DelThree()
Dim cLong As Long
cLong = Range(Cells(1, Columns.Count).Address).End(xlToLeft).Column()
For i = cLong To 1 Step -1
    If (i - 1) Mod 3 <> 0 Then
        Columns(i).EntireColumn.Delete
    End If
Next i
End Sub
Click to expand...



thanks for the response but was your macro something that could be used on any workbook to delete every 2nd and 3rd row as I cant get it to work? Thanks!
 
Upvote 0
Well, the line where cLong is set to the column count is predicated on you having data in the first row. You could enter this manually.

So, instead of

Code: 
cLong = Range(Cells(1, Columns.Count).Address).End(xlToLeft).Column()

it could be

Code: 
cLong = 26

Where 26 is the number of columns you want to include in your macro.

So, 26 means that the code would delete every 2nd and 3rd column from column A to Column Z.
 
Upvote 0
Thanks again! The macro works well. What would be the appropriate macro for something similar but with rows instead of columns and keeping every 2nd row in a pattern of 6 rows going down and repeating and deleting every 1st, 3rd, 4th, 5th and sixth row? Thanks!
 
Upvote 0
So, Delete Row 1, Keep Row 2, Delete Row 3-6

Then do you delete Row 7 and keep Row 8?
 
Upvote 0
Code: 
Sub DeleteEveryNthRow()
    'This will keep every 6th row, starting with row2, and delte the rest
    Dim rKeep As Range, rDelete As Range
    Dim i As Long
    With ActiveSheet
    
        'Set the rows to keep
        Set rKeep = .Rows(2)
        For i = 2 To .UsedRange.Rows.Count Step 6
            Set rKeep = Union(rKeep, .Rows(i))
        Next i
        
        'Subtract from UsedRange to delete rest
        Set rDelete = .Rows(1)
        For j = 1 To .UsedRange.Rows.Count
            If Intersect(.Rows(j), rKeep) Is Nothing Then
                Set rDelete = Union(rDelete, .Rows(j))
            End If
        Next j
    End With
    rDelete.Delete
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
counting duplicates error
Replies
1
Views
186
jasonb75
J
K
  • Question Question
Consolidate data from individual months by summing 2, 3 or more months together
Replies
0
Views
349
Kaleo
K
K0st4din
  • Question Question
Find matches by given criteria and divide by equal values up to 100%
Replies
3
Views
493
K0st4din
K0st4din
M
  • Question Question
Sentiment analysis - assigning positive, negative or neutral score
Replies
3
Views
2K
Peter_SSs
Peter_SSs
gtd526
  • Question Question
Formula works in one section but not in another???
2
Replies
10
Views
2K
gtd526
gtd526

Share this page

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