Macro Issue

H

henners7

New Member
Joined
Nov 14, 2012
Messages
8
Please could anyone help me with this, it's for my dissertation and aims to delete all the data in a spreadsheet that isn't required (as it is a standardized set of data which is unneeded).

I'm getting the error 'Compile error: Argument not optional' but have no idea why.

Any help would be so greatly appreciated.

Sub Delete_Rows()
'
' Delete_Rows Macro
'

'
Range( _
"2:2,3:3,4:4,5:5,7:7,13:13,19:19,20:20,21:21,22:22,27:27,30:30,31:31,32:32,33:33" _
).Select
Range("A33").Activate
ActiveWindow.SmallScroll Down:=18
Range( _
"2:2,3:3,4:4,5:5,7:7,13:13,19:19,20:20,21:21,22:22,27:27,30:30,31:31,32:32,33:33,42:42,43:43" _
).Select
Range("A43").Activate
ActiveWindow.SmallScroll Down:=21
Range( _
"2:2,3:3,4:4,5:5,7:7,13:13,19:19,20:20,21:21,22:22,27:27,30:30,31:31,32:32,33:33,42:42,43:43,44:44,45:45,51:51,52:52,53:53,54:54,59:59,67:67,70:70" _
).Select
Range("A70").Activate
Columns("A:A").ColumnWidth = 15.5
ActiveWindow.SmallScroll Down:=11
Union(Range( _
"2:2,3:3,4:4,5:5,7:7,13:13,19:19,20:20,21:21,22:22,27:27,30:30,31:31,32:32,33:33,42:42,43:43,44:44,45:45,51:51,52:52,53:53,54:54,59:59,67:67,70:70,71:71,74:74,72:72,76:76,80:80,82:82" _
)).Select
Range("A82").Activate
ActiveWindow.SmallScroll Down:=29
Union(Range( _
"87:87,2:2,3:3,4:4,5:5,7:7,13:13,19:19,20:20,21:21,22:22,27:27,30:30,31:31,32:32,33:33,42:42,43:43,44:44,45:45,51:51,52:52,53:53,54:54,59:59,67:67,70:70,71:71,74:74,72:72,76:76,80:80" _
), Rows("82:82")).Select
Range("A87").Activate
Union(Range( _
"87:87,96:96,97:97,99:99,103:103,105:105,107:107,109:109,110:110,2:2,3:3,4:4,5:5,7:7,13:13,19:19,20:20,21:21,22:22,27:27,30:30,31:31,32:32,33:33,42:42,43:43,44:44,45:45,51:51,52:52,53:53,54:54" _
), Range("59:59,67:67,70:70,71:71,74:74,72:72,76:76,80:80,82:82")).Select
Range("A110").Activate
ActiveWindow.SmallScroll Down:=20
Union(Range( _
"87:87,96:96,97:97,99:99,103:103,105:105,107:107,109:109,110:110,112:112,113:113,114:114,115:115,116:116,117:117,120:120,121:121,122:122,2:2,3:3,4:4,5:5,7:7,13:13,19:19,20:20,21:21,22:22,27:27,30:30,31:31,32:32" _
), Range( _
"33:33,42:42,43:43,44:44,45:45,51:51,52:52,53:53,54:54,59:59,67:67,70:70,71:71,74:74,72:72,76:76,80:80,82:82" _
)).Select
Range("A122").Activate
ActiveWindow.SmallScroll Down:=17
Union(Range( _
"87:87,96:96,97:97,99:99,103:103,105:105,107:107,109:109,110:110,112:112,113:113,114:114,115:115,116:116,117:117,120:120,121:121,122:122,123:123,128:128,134:134,140:140,141:141,142:142,143:143,2:2,3:3,4:4,5:5,7:7,13:13,19:19" _
), Range( _
"20:20,21:21,22:22,27:27,30:30,31:31,32:32,33:33,42:42,43:43,44:44,45:45,51:51,52:52,53:53,54:54,59:59,67:67,70:70,71:71,74:74,72:72,76:76,80:80,82:82" _
)).Select
Range("A143").Activate
ActiveWindow.SmallScroll Down:=24
Union(Range( _
"87:87,96:96,97:97,99:99,103:103,105:105,107:107,109:109,110:110,112:112,113:113,114:114,115:115,116:116,117:117,120:120,121:121,122:122,123:123,128:128,134:134,140:140,141:141,142:142,143:143,147:147,152:152,154:154,158:158,162:162,166:166,167:167" _
), Range( _
"168:168,169:169,170:170,171:171,172:172,2:2,3:3,4:4,5:5,7:7,13:13,19:19,20:20,21:21,22:22,27:27,30:30,31:31,32:32,33:33,42:42,43:43,44:44,45:45,51:51,52:52,53:53,54:54,59:59,67:67,70:70,71:71" _
), Range("74:74,72:72,76:76,80:80,82:82")).Select
Range("A172").Activate
ActiveWindow.SmallScroll Down:=29
Union(Range( _
"87:87,96:96,97:97,99:99,103:103,105:105,107:107,109:109,110:110,112:112,113:113,114:114,115:115,116:116,117:117,120:120,121:121,122:122,123:123,128:128,134:134,140:140,141:141,142:142,143:143,147:147,152:152,154:154,158:158,162:162,166:166,167:167" _
), Range( _
"168:168,169:169,170:170,171:171,172:172,174:174,181:181,183:183,187:187,198:198,2:2,3:3,4:4,5:5,7:7,13:13,19:19,20:20,21:21,22:22,27:27,30:30,31:31,32:32,33:33,42:42,43:43,44:44,45:45,51:51,52:52,53:53" _
), Range("54:54,59:59,67:67,70:70,71:71,74:74,72:72,76:76,80:80,82:82")).Select
Range("A198").Activate
ActiveWindow.SmallScroll Down:=20
Union(Range( _
"87:87,96:96,97:97,99:99,103:103,105:105,107:107,109:109,110:110,112:112,113:113,114:114,115:115,116:116,117:117,120:120,121:121,122:122,123:123,128:128,134:134,140:140,141:141,142:142,143:143,147:147,152:152,154:154,158:158,162:162,166:166,167:167" _
), Range( _
"168:168,169:169,170:170,171:171,172:172,174:174,181:181,183:183,187:187,198:198,202:202,203:203,204:204,205:205,206:206,207:207,208:208,209:209,210:210,2:2,3:3,4:4,5:5,7:7,13:13,19:19,20:20,21:21,22:22,27:27,30:30,31:31" _
), Range( _
"32:32,33:33,42:42,43:43,44:44,45:45,51:51,52:52,53:53,54:54,59:59,67:67,70:70,71:71,74:74,72:72,76:76,80:80,82:82" _
)).Select
Range("A210").Activate
ActiveWindow.SmallScroll Down:=12
Union(Range( _
"87:87,96:96,97:97,99:99,103:103,105:105,107:107,109:109,110:110,112:112,113:113,114:114,115:115,116:116,117:117,120:120,121:121,122:122,123:123,128:128,134:134,140:140,141:141,142:142,143:143,147:147,152:152,154:154,158:158,162:162,166:166,167:167" _
), Range( _
"168:168,169:169,170:170,171:171,172:172,174:174,181:181,183:183,187:187,198:198,202:202,203:203,204:204,205:205,206:206,207:207,208:208,209:209,210:210,215:215,2:2,3:3,4:4,5:5,7:7,13:13,19:19,20:20,21:21,22:22,27:27,30:30" _
), Range( _
"31:31,32:32,33:33,42:42,43:43,44:44,45:45,51:51,52:52,53:53,54:54,59:59,67:67,70:70,71:71,74:74,72:72,76:76,80:80,82:82" _
)).Select
Range("A215").Activate
Union(Range( _
"87:87,96:96,97:97,99:99,103:103,105:105,107:107,109:109,110:110,112:112,113:113,114:114,115:115,116:116,117:117,120:120,121:121,122:122,123:123,128:128,134:134,140:140,141:141,142:142,143:143,147:147,152:152,154:154,158:158,162:162,166:166,167:167" _
), Range( _
"168:168,169:169,170:170,171:171,172:172,174:174,181:181,183:183,187:187,198:198,202:202,203:203,204:204,205:205,206:206,207:207,208:208,209:209,210:210,215:215,221:221,2:2,3:3,4:4,5:5,7:7,13:13,19:19,20:20,21:21,22:22,27:27" _
), Range( _
"30:30,31:31,32:32,33:33,42:42,43:43,44:44,45:45,51:51,52:52,53:53,54:54,59:59,67:67,70:70,71:71,74:74,72:72,76:76,80:80,82:82" _
)).Select
Range("A221").Activate
Union(Range( _
"87:87,96:96,97:97,99:99,103:103,105:105,107:107,109:109,110:110,112:112,113:113,114:114,115:115,116:116,117:117,120:120,121:121,122:122,123:123,128:128,134:134,140:140,141:141,142:142,143:143,147:147,152:152,154:154,158:158,162:162,166:166,167:167" _
), Range( _
"168:168,169:169,170:170,171:171,172:172,174:174,181:181,183:183,187:187,198:198,202:202,203:203,204:204,205:205,206:206,207:207,208:208,209:209,210:210,215:215,221:221,224:224,225:225,226:226,2:2,3:3,4:4,5:5,7:7,13:13,19:19,20:20" _
), Range( _
"21:21,22:22,27:27,30:30,31:31,32:32,33:33,42:42,43:43,44:44,45:45,51:51,52:52,53:53,54:54,59:59,67:67,70:70,71:71,74:74,72:72,76:76,80:80,82:82" _
)).Select
Range("A226").Activate
ActiveWindow.SmallScroll Down:=11
Union(Range( _
"87:87,96:96,97:97,99:99,103:103,105:105,107:107,109:109,110:110,112:112,113:113,114:114,115:115,116:116,117:117,120:120,121:121,122:122,123:123,128:128,134:134,140:140,141:141,142:142,143:143,147:147,152:152,154:154,158:158,162:162,166:166,167:167" _
), Range( _
"168:168,169:169,170:170,171:171,172:172,174:174,181:181,183:183,187:187,198:198,202:202,203:203,204:204,205:205,206:206,207:207,208:208,209:209,210:210,215:215,221:221,224:224,225:225,226:226,233:233,236:236,237:237,238:238,239:239,240:240,241:241,242:242" _
), Range( _
"2:2,3:3,4:4,5:5,7:7,13:13,19:19,20:20,21:21,22:22,27:27,30:30,31:31,32:32,33:33,42:42,43:43,44:44,45:45,51:51,52:52,53:53,54:54,59:59,67:67,70:70,71:71,74:74,72:72,76:76,80:80,82:82" _
)).Select
Range("A242").Activate
ActiveWindow.SmallScroll Down:=29
Union(Range( _
"87:87,96:96,97:97,99:99,103:103,105:105,107:107,109:109,110:110,112:112,113:113,114:114,115:115,116:116,117:117,120:120,121:121,122:122,123:123,128:128,134:134,140:140,141:141,142:142,143:143,147:147,152:152,154:154,158:158,162:162,166:166,167:167" _
), Range( _
"168:168,169:169,170:170,171:171,172:172,174:174,181:181,183:183,187:187,198:198,202:202,203:203,204:204,205:205,206:206,207:207,208:208,209:209,210:210,215:215,221:221,224:224,225:225,226:226,233:233,236:236,237:237,238:238,239:239,240:240,241:241,242:242" _
), Range( _
"244:244,245:245,246:246,247:247,249:249,250:250,251:251,252:252,254:254,255:255,256:256,257:257,258:258,259:259,261:261,265:265,269:269,270:270,2:2,3:3,4:4,5:5,7:7,13:13,19:19,20:20,21:21,22:22,27:27,30:30,31:31,32:32" _
), Range( _
"33:33,42:42,43:43,44:44,45:45,51:51,52:52,53:53,54:54,59:59,67:67,70:70,71:71,74:74,72:72,76:76,80:80,82:82" _
)).Select
Range("A270").Activate
ActiveWindow.SmallScroll Down:=10
Union(Range( _
"87:87,96:96,97:97,99:99,103:103,105:105,107:107,109:109,110:110,112:112,113:113,114:114,115:115,116:116,117:117,120:120,121:121,122:122,123:123,128:128,134:134,140:140,141:141,142:142,143:143,147:147,152:152,154:154,158:158,162:162,166:166,167:167" _
), Range( _
"168:168,169:169,170:170,171:171,172:172,174:174,181:181,183:183,187:187,198:198,202:202,203:203,204:204,205:205,206:206,207:207,208:208,209:209,210:210,215:215,221:221,224:224,225:225,226:226,233:233,236:236,237:237,238:238,239:239,240:240,241:241,242:242" _
), Range( _
"244:244,245:245,246:246,247:247,249:249,250:250,251:251,252:252,254:254,255:255,256:256,257:257,258:258,259:259,261:261,265:265,269:269,270:270,272:272,273:273,274:274,275:275,276:276,2:2,3:3,4:4,5:5,7:7,13:13,19:19,20:20,21:21" _
), Range( _
"22:22,27:27,30:30,31:31,32:32,33:33,42:42,43:43,44:44,45:45,51:51,52:52,53:53,54:54,59:59,67:67,70:70,71:71,74:74,72:72,76:76,80:80,82:82" _
)).Select
Range("A276").Activate
Union(Range( _
"87:87,96:96,97:97,99:99,103:103,105:105,107:107,109:109,110:110,112:112,113:113,114:114,115:115,116:116,117:117,120:120,121:121,122:122,123:123,128:128,134:134,140:140,141:141,142:142,143:143,147:147,152:152,154:154,158:158,162:162,166:166,167:167" _
), Range( _
"168:168,169:169,170:170,171:171,172:172,174:174,181:181,183:183,187:187,198:198,202:202,203:203,204:204,205:205,206:206,207:207,208:208,209:209,210:210,215:215,221:221,224:224,225:225,226:226,233:233,236:236,237:237,238:238,239:239,240:240,241:241,242:242" _
), Range( _
"244:244,245:245,246:246,247:247,249:249,250:250,251:251,252:252,254:254,255:255,256:256,257:257,258:258,259:259,261:261,265:265,269:269,270:270,272:272,273:273,274:274,275:275,276:276,277:277,2:2,3:3,4:4,5:5,7:7,13:13,19:19,20:20" _
), Range( _
"21:21,22:22,27:27,30:30,31:31,32:32,33:33,42:42,43:43,44:44,45:45,51:51,52:52,53:53,54:54,59:59,67:67,70:70,71:71,74:74,72:72,76:76,80:80,82:82" _
)).Select
Range("A277").Activate
ActiveWindow.SmallScroll Down:=15
Union(Range( _
"87:87,96:96,97:97,99:99,103:103,105:105,107:107,109:109,110:110,112:112,113:113,114:114,115:115,116:116,117:117,120:120,121:121,122:122,123:123,128:128,134:134,140:140,141:141,142:142,143:143,147:147,152:152,154:154,158:158,162:162,166:166,167:167" _
), Range( _
"168:168,169:169,170:170,171:171,172:172,174:174,181:181,183:183,187:187,198:198,202:202,203:203,204:204,205:205,206:206,207:207,208:208,209:209,210:210,215:215,221:221,224:224,225:225,226:226,233:233,236:236,237:237,238:238,239:239,240:240,241:241,242:242" _
), Range( _
"244:244,245:245,246:246,247:247,249:249,250:250,251:251,252:252,254:254,255:255,256:256,257:257,258:258,259:259,261:261,265:265,269:269,270:270,272:272,273:273,274:274,275:275,276:276,277:277,281:281,285:285,293:293,294:294,295:295,296:296,2:2,3:3" _
), Range( _
"4:4,5:5,7:7,13:13,19:19,20:20,21:21,22:22,27:27,30:30,31:31,32:32,33:33,42:42,43:43,44:44,45:45,51:51,52:52,53:53,54:54,59:59,67:67,70:70,71:71,74:74,72:72,76:76,80:80,82:82" _
)).Select
Range("A296").Activate
ActiveWindow.SmallScroll Down:=31
Union(Range( _
"87:87,96:96,97:97,99:99,103:103,105:105,107:107,109:109,110:110,112:112,113:113,114:114,115:115,116:116,117:117,120:120,121:121,122:122,123:123,128:128,134:134,140:140,141:141,142:142,143:143,147:147,152:152,154:154,158:158,162:162,166:166,167:167" _
), Range( _
"168:168,169:169,170:170,171:171,172:172,174:174,181:181,183:183,187:187,198:198,202:202,203:203,204:204,205:205,206:206,207:207,208:208,209:209,210:210,215:215,221:221,224:224,225:225,226:226,233:233,236:236,237:237,238:238,239:239,240:240,241:241,242:242" _
), Range( _
"244:244,245:245,246:246,247:247,249:249,250:250,251:251,252:252,254:254,255:255,256:256,257:257,258:258,259:259,261:261,265:265,269:269,270:270,272:272,273:273,274:274,275:275,276:276,277:277,281:281,285:285,293:293,294:294,295:295,296:296,300:300,303:303" _
), Range( _
"308:308,309:309,310:310,311:311,314:314,317:317,322:322,326:326,329:329,2:2,3:3,4:4,5:5,7:7,13:13,19:19,20:20,21:21,22:22,27:27,30:30,31:31,32:32,33:33,42:42,43:43,44:44,45:45,51:51,52:52,53:53,54:54" _
), Range("59:59,67:67,70:70,71:71,74:74,72:72,76:76,80:80,82:82")).Select
Range("A329").Activate
ActiveWindow.SmallScroll Down:=27
Union(Range( _
"87:87,96:96,97:97,99:99,103:103,105:105,107:107,109:109,110:110,112:112,113:113,114:114,115:115,116:116,117:117,120:120,121:121,122:122,123:123,128:128,134:134,140:140,141:141,142:142,143:143,147:147,152:152,154:154,158:158,162:162,166:166,167:167" _
), Range( _
"168:168,169:169,170:170,171:171,172:172,174:174,181:181,183:183,187:187,198:198,202:202,203:203,204:204,205:205,206:206,207:207,208:208,209:209,210:210,215:215,221:221,224:224,225:225,226:226,233:233,236:236,237:237,238:238,239:239,240:240,241:241,242:242" _
), Range( _
"244:244,245:245,246:246,247:247,249:249,250:250,251:251,252:252,254:254,255:255,256:256,257:257,258:258,259:259,261:261,265:265,269:269,270:270,272:272,273:273,274:274,275:275,276:276,277:277,281:281,285:285,293:293,294:294,295:295,296:296,300:300,303:303" _
), Range( _
"308:308,309:309,310:310,311:311,314:314,317:317,322:322,326:326,329:329,333:333,334:334,335:335,336:336,342:342,343:343,344:344,345:345,346:346,347:347,348:348,349:349,2:2,3:3,4:4,5:5,7:7,13:13,19:19,20:20,21:21,22:22,27:27" _
), Range( _
"30:30,31:31,32:32,33:33,42:42,43:43,44:44,45:45,51:51,52:52,53:53,54:54,59:59,67:67,70:70,71:71,74:74,72:72,76:76,80:80,82:82" _
)).Select
Range("A349").Activate
ActiveWindow.SmallScroll Down:=20
Union(Range( _
"87:87,96:96,97:97,99:99,103:103,105:105,107:107,109:109,110:110,112:112,113:113,114:114,115:115,116:116,117:117,120:120,121:121,122:122,123:123,128:128,134:134,140:140,141:141,142:142,143:143,147:147,152:152,154:154,158:158,162:162,166:166,167:167" _
), Range( _
"168:168,169:169,170:170,171:171,172:172,174:174,181:181,183:183,187:187,198:198,202:202,203:203,204:204,205:205,206:206,207:207,208:208,209:209,210:210,215:215,221:221,224:224,225:225,226:226,233:233,236:236,237:237,238:238,239:239,240:240,241:241,242:242" _
), Range( _
"244:244,245:245,246:246,247:247,249:249,250:250,251:251,252:252,254:254,255:255,256:256,257:257,258:258,259:259,261:261,265:265,269:269,270:270,272:272,273:273,274:274,275:275,276:276,277:277,281:281,285:285,293:293,294:294,295:295,296:296,300:300,303:303" _
), Range( _
"308:308,309:309,310:310,311:311,314:314,317:317,322:322,326:326,329:329,333:333,334:334,335:335,336:336,342:342,343:343,344:344,345:345,346:346,347:347,348:348,349:349,352:352,355:355,358:358,361:361,362:362,363:363,364:364,367:367,368:368,369:369,370:370" _
), Range( _
"2:2,3:3,4:4,5:5,7:7,13:13,19:19,20:20,21:21,22:22,27:27,30:30,31:31,32:32,33:33,42:42,43:43,44:44,45:45,51:51,52:52,53:53,54:54,59:59,67:67,70:70,71:71,74:74,72:72,76:76,80:80,82:82" _
)).Select
Range("A370").Activate
ActiveWindow.SmallScroll Down:=23
Union(Range( _
"87:87,96:96,97:97,99:99,103:103,105:105,107:107,109:109,110:110,112:112,113:113,114:114,115:115,116:116,117:117,120:120,121:121,122:122,123:123,128:128,134:134,140:140,141:141,142:142,143:143,147:147,152:152,154:154,158:158,162:162,166:166,167:167" _
), Range( _
"168:168,169:169,170:170,171:171,172:172,174:174,181:181,183:183,187:187,198:198,202:202,203:203,204:204,205:205,206:206,207:207,208:208,209:209,210:210,215:215,221:221,224:224,225:225,226:226,233:233,236:236,237:237,238:238,239:239,240:240,241:241,242:242" _
), Range( _
"244:244,245:245,246:246,247:247,249:249,250:250,251:251,252:252,254:254,255:255,256:256,257:257,258:258,259:259,261:261,265:265,269:269,270:270,272:272,273:273,274:274,275:275,276:276,277:277,281:281,285:285,293:293,294:294,295:295,296:296,300:300,303:303" _
), Range( _
"308:308,309:309,310:310,311:311,314:314,317:317,322:322,326:326,329:329,333:333,334:334,335:335,336:336,342:342,343:343,344:344,345:345,346:346,347:347,348:348,349:349,352:352,355:355,358:358,361:361,362:362,363:363,364:364,367:367,368:368,369:369,370:370" _
), Range( _
"375:375,376:376,377:377,378:378,2:2,3:3,4:4,5:5,7:7,13:13,19:19,20:20,21:21,22:22,27:27,30:30,31:31,32:32,33:33,42:42,43:43,44:44,45:45,51:51,52:52,53:53,54:54,59:59,67:67,70:70,71:71,74:74" _
), Range("72:72,76:76,80:80,82:82")).Select
Range("A378").Activate
Union(Range( _
"87:87,96:96,97:97,99:99,103:103,105:105,107:107,109:109,110:110,112:112,113:113,114:114,115:115,116:116,117:117,120:120,121:121,122:122,123:123,128:128,134:134,140:140,141:141,142:142,143:143,147:147,152:152,154:154,158:158,162:162,166:166,167:167" _
), Range( _
"168:168,169:169,170:170,171:171,172:172,174:174,181:181,183:183,187:187,198:198,202:202,203:203,204:204,205:205,206:206,207:207,208:208,209:209,210:210,215:215,221:221,224:224,225:225,226:226,233:233,236:236,237:237,238:238,239:239,240:240,241:241,242:242" _
), Range( _
"244:244,245:245,246:246,247:247,249:249,250:250,251:251,252:252,254:254,255:255,256:256,257:257,258:258,259:259,261:261,265:265,269:269,270:270,272:272,273:273,274:274,275:275,276:276,277:277,281:281,285:285,293:293,294:294,295:295,296:296,300:300,303:303" _
), Range( _
"308:308,309:309,310:310,311:311,314:314,317:317,322:322,326:326,329:329,333:333,334:334,335:335,336:336,342:342,343:343,344:344,345:345,346:346,347:347,348:348,349:349,352:352,355:355,358:358,361:361,362:362,363:363,364:364,367:367,368:368,369:369,370:370" _
), Range( _
"375:375,376:376,377:377,378:378,379:379,383:383,385:385,386:386,387:387,388:388,2:2,3:3,4:4,5:5,7:7,13:13,19:19,20:20,21:21,22:22,27:27,30:30,31:31,32:32,33:33,42:42,43:43,44:44,45:45,51:51,52:52,53:53" _
), Range("54:54,59:59,67:67,70:70,71:71,74:74,72:72,76:76,80:80,82:82")).Select
Range("A388").Activate
Union(Range( _
"87:87,96:96,97:97,99:99,103:103,105:105,107:107,109:109,110:110,112:112,113:113,114:114,115:115,116:116,117:117,120:120,121:121,122:122,123:123,128:128,134:134,140:140,141:141,142:142,143:143,147:147,152:152,154:154,158:158,162:162,166:166,167:167" _
), Range( _
"168:168,169:169,170:170,171:171,172:172,174:174,181:181,183:183,187:187,198:198,202:202,203:203,204:204,205:205,206:206,207:207,208:208,209:209,210:210,215:215,221:221,224:224,225:225,226:226,233:233,236:236,237:237,238:238,239:239,240:240,241:241,242:242" _
), Range( _
"244:244,245:245,246:246,247:247,249:249,250:250,251:251,252:252,254:254,255:255,256:256,257:257,258:258,259:259,261:261,265:265,269:269,270:270,272:272,273:273,274:274,275:275,276:276,277:277,281:281,285:285,293:293,294:294,295:295,296:296,300:300,303:303" _
), Range( _
"308:308,309:309,310:310,311:311,314:314,317:317,322:322,326:326,329:329,333:333,334:334,335:335,336:336,342:342,343:343,344:344,345:345,346:346,347:347,348:348,349:349,352:352,355:355,358:358,361:361,362:362,363:363,364:364,367:367,368:368,369:369,370:370" _
), Range( _
"375:375,376:376,377:377,378:378,379:379,383:383,385:385,386:386,387:387,388:388,391:391,395:395,399:399,400:400,2:2,3:3,4:4,5:5,7:7,13:13,19:19,20:20,21:21,22:22,27:27,30:30,31:31,32:32,33:33,42:42,43:43,44:44" _
), Range( _
"45:45,51:51,52:52,53:53,54:54,59:59,67:67,70:70,71:71,74:74,72:72,76:76,80:80,82:82" _
)).Select
Range("A400").Activate
ActiveWindow.SmallScroll Down:=29
Union(Range( _
"87:87,96:96,97:97,99:99,103:103,105:105,107:107,109:109,110:110,112:112,113:113,114:114,115:115,116:116,117:117,120:120,121:121,122:122,123:123,128:128,134:134,140:140,141:141,142:142,143:143,147:147,152:152,154:154,158:158,162:162,166:166,167:167" _
), Range( _
"168:168,169:169,170:170,171:171,172:172,174:174,181:181,183:183,187:187,198:198,202:202,203:203,204:204,205:205,206:206,207:207,208:208,209:209,210:210,215:215,221:221,224:224,225:225,226:226,233:233,236:236,237:237,238:238,239:239,240:240,241:241,242:242" _
), Range( _
"244:244,245:245,246:246,247:247,249:249,250:250,251:251,252:252,254:254,255:255,256:256,257:257,258:258,259:259,261:261,265:265,269:269,270:270,272:272,273:273,274:274,275:275,276:276,277:277,281:281,285:285,293:293,294:294,295:295,296:296,300:300,303:303" _
), Range( _
"308:308,309:309,310:310,311:311,314:314,317:317,322:322,326:326,329:329,333:333,334:334,335:335,336:336,342:342,343:343,344:344,345:345,346:346,347:347,348:348,349:349,352:352,355:355,358:358,361:361,362:362,363:363,364:364,367:367,368:368,369:369,370:370" _
), Range( _
"375:375,376:376,377:377,378:378,379:379,383:383,385:385,386:386,387:387,388:388,391:391,395:395,399:399,400:400,401:401,402:402,405:405,411:411,416:416,420:420,2:2,3:3,4:4,5:5,7:7,13:13,19:19,20:20,21:21,22:22,27:27,30:30" _
), Range( _
"31:31,32:32,33:33,42:42,43:43,44:44,45:45,51:51,52:52,53:53,54:54,59:59,67:67,70:70,71:71,74:74,72:72,76:76,80:80,82:82" _
)).Select
Range("A420").Activate
ActiveWindow.SmallScroll Down:=24
Union(Range( _
"87:87,96:96,97:97,99:99,103:103,105:105,107:107,109:109,110:110,112:112,113:113,114:114,115:115,116:116,117:117,120:120,121:121,122:122,123:123,128:128,134:134,140:140,141:141,142:142,143:143,147:147,152:152,154:154,158:158,162:162,166:166,167:167" _
), Range( _
"168:168,169:169,170:170,171:171,172:172,174:174,181:181,183:183,187:187,198:198,202:202,203:203,204:204,205:205,206:206,207:207,208:208,209:209,210:210,215:215,221:221,224:224,225:225,226:226,233:233,236:236,237:237,238:238,239:239,240:240,241:241,242:242" _
), Range( _
"244:244,245:245,246:246,247:247,249:249,250:250,251:251,252:252,254:254,255:255,256:256,257:257,258:258,259:259,261:261,265:265,269:269,270:270,272:272,273:273,274:274,275:275,276:276,277:277,281:281,285:285,293:293,294:294,295:295,296:296,300:300,303:303" _
), Range( _
"308:308,309:309,310:310,311:311,314:314,317:317,322:322,326:326,329:329,333:333,334:334,335:335,336:336,342:342,343:343,344:344,345:345,346:346,347:347,348:348,349:349,352:352,355:355,358:358,361:361,362:362,363:363,364:364,367:367,368:368,369:369,370:370" _
), Range( _
"375:375,376:376,377:377,378:378,379:379,383:383,385:385,386:386,387:387,388:388,391:391,395:395,399:399,400:400,401:401,402:402,405:405,411:411,416:416,420:420,424:424,428:428,431:431,436:436,444:444,441:441,450:450,2:2,3:3,4:4,5:5,7:7" _
), Range( _
"13:13,19:19,20:20,21:21,22:22,27:27,30:30,31:31,32:32,33:33,42:42,43:43,44:44,45:45,51:51,52:52,53:53,54:54,59:59,67:67,70:70,71:71,74:74,72:72,76:76,80:80,82:82" _
)).Select
Range("A450").Activate
Selection.Delete Shift:=xlUp
ActiveWindow.SmallScroll Down:=-175
Rows("264:379").Select
Selection.Delete Shift:=xlUp
End Sub
 
Sort by date Sort by votes
I've already identified the useless ones by going through and noting down the number of the row (through cross-examination with a list of the useful ones)
 
Upvote 0

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
So I tried that idea;
Sub Delete_Rows()
'
' Delete_Rows Macro
'

'
Range("2:2,3:3,4:4,5:5,7:7,13:13,19:19,20:20,21:21,22:22,27:27,30:30,31:31,32:32,33:33,42:42,43:43,44:44,45:45,51:51,52:52,53:53,54:54,59:59,67:67,70:70,71:71,74:74,72:72,76:76,80:80,82:82,87:87,96:96,97:97,99:99,103:103,105:105,107:107,109:109,110:110,112:112,113:113,114:114,115:115,116:116,117:117,120:120 ,121:121,122:122,123:123,128:128,134:134,140:140,141:141,142:142,143:143,147:147,152:152,154:154,158:158,162:162,166:166,167:167,168:168,169:169,170:170,171:171,172:172,174:174,181:181,183:183,187:187,198:198,202:202,203:203,204:204,205:205,206:206 ,207:207,208:208,209:209,210:210,215:215,221:221,224,225:225,226:226,233:233,236:236,237:237,238:238,239:239,240:240,241:241,242:242,244:244,245:245,246:246,247:247,249:249,250:250,251:251,252:252,254:254,255:255,256:256,257:257,258:258,259:259,261:261,265:265,269:269,270:270,272:272,273:273,274:274,275:275,276:276,277:277,281:281,285:285,293:293,294:294,295:295,296:296,300:300,303:303").Delete

Range("308:308,309:309,310:310,311:311,314:314,317:317,322:322,326:326,329:329,333:333,334:334,335:335,336:336,342:342,343:343,344:344,345:345,346:346,347:347,348:348,349:349,352:352,355:355,358:358,361:361,362:362,363:363,364:364,367:367,368:368,369:369,370:370,375:375,376:376,377:377,378:378,379:379,383:383,385:385,386:386,387:387,388:388,391:391,395:395,399:399,400:400401:401 ,402:402,405:405,411:411,416:416,420:420,424:424,428:428,431:431,436:436,444:444,441:441,450:450").Delete

End Sub

It now tells me;

"Rune-time error '1004':
Method 'Range' of object '_Global' failed.
 
Upvote 0
henners7 said:
I've already identified the useless ones by going through and noting down the number of the row (through cross-examination with a list of the useful ones)
Click to expand...

What Norie meant was what is on those rows that led you to select them for deletion? For example, do they all contain a certain word in a certain Column or, perhaps, do they not contain a certain word there? The point of my lead-off comment and Norie's question is that VB coding can be made to eliminate lots of dog-work like you are apparently doing and if you can tell us what you are using to decide on the rows to be deleted, someone here would be happy to write the macro for you (it will be a lot shorter and quicker than what you seem to be doing now).
 
Upvote 0
henners7 said:
So I tried that idea;
Sub Delete_Rows()
'
' Delete_Rows Macro
'

'
Range("2:2,3:3,4:4,5:5,7:7,13:13,19:19,20:20,21:21,22:22,27:27,30:30,31:31,32:32,33:33,42:42,43:43,44:44,45:45,51:51,52:52,53:53,54:54,59:59,67:67,70:70,71:71,74:74,72:72,76:76,80:80,82:82,87:87,96:96,97:97,99:99,103:103,105:105,107:107,109:109,110:110,112:112,113:113,114:114,115:115,116:116,117:117,120:120 ,121:121,122:122,123:123,128:128,134:134,140:140,141:141,142:142,143:143,147:147,152:152,154:154,158:158,162:162,166:166,167:167,168:168,169:169,170:170,171:171,172:172,174:174,181:181,183:183,187:187,198:198,202:202,203:203,204:204,205:205,206:206 ,207:207,208:208,209:209,210:210,215:215,221:221,224,225:225,226:226,233:233,236:236,237:237,238:238,239:239,240:240,241:241,242:242,244:244,245:245,246:246,247:247,249:249,250:250,251:251,252:252,254:254,255:255,256:256,257:257,258:258,259:259,261:261,265:265,269:269,270:270,272:272,273:273,274:274,275:275,276:276,277:277,281:281,285:285,293:293,294:294,295:295,296:296,300:300,303:303").Delete

Range("308:308,309:309,310:310,311:311,314:314,317:317,322:322,326:326,329:329,333:333,334:334,335:335,336:336,342:342,343:343,344:344,345:345,346:346,347:347,348:348,349:349,352:352,355:355,358:358,361:361,362:362,363:363,364:364,367:367,368:368,369:369,370:370,375:375,376:376,377:377,378:378,379:379,383:383,385:385,386:386,387:387,388:388,391:391,395:395,399:399,400:400401:401 ,402:402,405:405,411:411,416:416,420:420,424:424,428:428,431:431,436:436,444:444,441:441,450:450").Delete

End Sub

It now tells me;

"Rune-time error '1004':
Method 'Range' of object '_Global' failed.
Click to expand...
I think you are running into the problem I mention about how long the argument the Range call can be... I didn't count it, but that first one looks to be longer than 255 characters. If you can show us a sample of your data (both the data you want to keep and the data you want to eliminate) and tell us how you are choosing the row numbers you have selected for deletion (what is in, or not in, the cells of those rows that makes you know you want to delete them?), someone here would be more than willing to try and make a short macro to do all the work you are trying to do manually.
 
Upvote 0
There's no easy way to distinguish useful data compared to useless data. The data that needs to be removed are just in the exact same rows each time, despite the content changing.

Even with lines smaller than 255 chars it still gives me the same error;

Sub Delete_Rows()
'
' Delete_Rows Macro
'

'
Range("2:2,3:3,4:4,5:5,7:7,13:13,19:19,20:20,21:21,22:22,27:27,30:30,31:31,32:32,33:33,42:42,43:43,44:44,45:45,51:51,52:52,53:53,54:54,59:59,67:67,70:70,71:71,74:74,72:72,76:76,80:80").Delete

Range("82:82,87:87,96:96,97:97,99:99,103:103,105:105,107:107,109:109,110:110,112:112,113:113,114:114,115:115,116:116,117:117,120:120 ,121:121,122:122,123:123,128:128,134:134,140:140").Delete

Range("141:141,142:142,143:143,147:147,152:152,154:154,158:158,162:162,166:166,167:167,168:168,169:169,170:170,171:171,172:172,174:174,181:181,183:183,187:187,198:198,202:202").Delete

Range("203:203,204:204,205:205,206:206,207:207,208:208,209:209,210:210,215:215,221:221,224,225:225,226:226,233:233,236:236,237:237,238:238,239:239,240:240,241:241,242:242,244:244").Delete

Range("245:245,246:246,247:247,249:249,250:250,251:251,252:252,254:254,255:255,256:256,257:257,258:258,259:259,261:261,265:265,269:269,270:270,272:272,273:273,274:274,275:275,276:276,277:277").Delete

Range("281:281,285:285,293:293,294:294,295:295,296:296,300:300,303:303,308:308,309:309,310:310,311:311,314:314,317:317,322:322,326:326,329:329").Delete

Range("333:333,334:334,335:335,336:336,342:342,343:343,344:344,345:345,346:346,347:347,348:348,349:349,352:352,355:355,358:358,361:361,362:362,363:363").Delete

Range("364:364,367:367,368:368,369:369,370:370,375:375,376:376,377:377,378:378,379:379,383:383,385:385,386:386,387:387,388:388,391:391,395:395,399:399").Delete

Range("400:400401:401 ,402:402,405:405,411:411,416:416,420:420,424:424,428:428,431:431,436:436,444:444,441:441,450:450").Delete

End Sub
 
Upvote 0
henners7 said:
There's no easy way to distinguish useful data compared to useless data. The data that needs to be removed are just in the exact same rows each time, despite the content changing.

Even with lines smaller than 255 chars it still gives me the same error;

Sub Delete_Rows()
'
' Delete_Rows Macro
'

'
Range("2:2,3:3,4:4,5:5,7:7,13:13,19:19,20:20,21:21,22:22,27:27,30:30,31:31,32:32,33:33,42:42,43:43,44:44,45:45,51:51,52:52,53:53,54:54,59:59,67:67,70:70,71:71,74:74,72:72,76:76,80:80").Delete

Range("82:82,87:87,96:96,97:97,99:99,103:103,105:105,107:107,109:109,110:110,112:112,113:113,114:114,115:115,116:116,117:117,120:120 ,121:121,122:122,123:123,128:128,134:134,140:140").Delete

Range("141:141,142:142,143:143,147:147,152:152,154:154,158:158,162:162,166:166,167:167,168:168,169:169,170:170,171:171,172:172,174:174,181:181,183:183,187:187,198:198,202:202").Delete

Range("203:203,204:204,205:205,206:206,207:207,208:208,209:209,210:210,215:215,221:221,224,225:225,226:226,233:233,236:236,237:237,238:238,239:239,240:240,241:241,242:242,244:244").Delete

Range("245:245,246:246,247:247,249:249,250:250,251:251,252:252,254:254,255:255,256:256,257:257,258:258,259:259,261:261,265:265,269:269,270:270,272:272,273:273,274:274,275:275,276:276,277:277").Delete

Range("281:281,285:285,293:293,294:294,295:295,296:296,300:300,303:303,308:308,309:309,310:310,311:311,314:314,317:317,322:322,326:326,329:329").Delete

Range("333:333,334:334,335:335,336:336,342:342,343:343,344:344,345:345,346:346,347:347,348:348,349:349,352:352,355:355,358:358,361:361,362:362,363:363").Delete

Range("364:364,367:367,368:368,369:369,370:370,375:375,376:376,377:377,378:378,379:379,383:383,385:385,386:386,387:387,388:388,391:391,395:395,399:399").Delete

Range("400:400401:401 ,402:402,405:405,411:411,416:416,420:420,424:424,428:428,431:431,436:436,444:444,441:441,450:450").Delete

End Sub
Click to expand...
The fact that the row are always the same is a condition that can be used. Here is a macro that you can use...
Rich (BB code): 
Sub DeleteUnwantedRows()
  Dim X As Long, DeleteThese() As String
  Const RowsToDelete As String = "2 3 4 5 7 13 19 20 21 22 27 30 31 32 33 42 43 44 45"
  DeleteThese = Split(RowsToDelete)
  Application.ScreenUpdating = False
  For X = UBound(DeleteThese) To 0 Step -1
    Rows(DeleteThese(X)).Delete
  Next
  Application.ScreenUpdating = True
  MsgBox "Done!"
End Sub
The only thing you have to do is list the line numbers in a single-space separated list as shown in red above (the list can be up to 2 billion characters long, so you should not run into any limits). Note the numbers are listed singly, by themselves, without colons or commas. The code could take some time to execute, so I put a message box at the end to signal you when the code has finished.




henners7 said:
Even with lines smaller than 255 chars it still gives me the same error;

Range("400:400401:01 ,402:402,405:405,411:411,416:416,420:420,424:424,428:428,431:431,436:436,444:444,441:441,450:450").Delete

End Sub
Click to expand...
I'm guessing all but the last set of row worked... you omitted a comma in the text I highlighted in red for that statment.
 
Upvote 0
Rick Rothstein said:
I'm guessing all but the last set of row worked... you omitted a comma in the text I highlighted in red for that statment.
Click to expand...

Actually, the code would have died on the first statement as you added a space where it doesn't belong (see the red text)...
Rich (BB code): 
Range("2:2,3:3,4:4,5:5,7:7,13:13,19:19,20:20,21:21,22:22,27:27,30:30,31:31,32:32,33:33,42:42,
43:43,44:44,45:45,51:51,52:52,53:5 3,54:54,59:59,67:67,70:70,71:71,74:74,72:72,76:76,80:80").Delete
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,215,357
Messages
6,124,483
Members
449,165
Latest member
ChipDude83

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