PDA

View Full Version : code sequence works manually but not as a macro?



mal
03-15-2015, 04:28 PM
I have a file of the performance of 1800 ETFs that is sorted by "categories". I want to remove some categories with this code: (1) Find All "category X" (finds the group of rows containing category X) (2) Select all with shift+click and (3) Delete (selected) rows. Works all the time. But as a macro it fails. Any ideas?
thanks, Mal

Paul_Hossler
03-15-2015, 04:52 PM
What was your macro?

mal
03-16-2015, 08:07 AM
The macro was created by recording the three steps given for each category I wanted removed. That is I recorded going through the following sequence about 5 times: (1) Find All [name of category] (2) Select the group of cells found [they are contiguous] and (3) Delete Rows.
Mal

Paul_Hossler
03-16-2015, 09:47 AM
Actually what I meant was could you post the macro code that you are having trouble with?

Use the [#] button and paste the macro between the [CODE] tags

jolivanes
03-18-2015, 12:15 AM
I don't know if I understand you right but in case I do, you could try this on a copy of your workbook.
Change Sheet Name, Column references and categories as required.

Sub Maybe()
Dim arrFind As Variant
Application.ScreenUpdating = False
arrFind = Array("category x", "category y", "category z")
With Sheets("Sheet2")
.AutoFilterMode = False
.UsedRange.AutoFilter 1, arrFind, xlFilterValues
.Range("A1:A" & .Cells(.Rows.Count, 1).End(xlUp).Row).SpecialCells(12).EntireRow.Delete
End With
Sheets("Sheet2").AutoFilterMode = False
Application.ScreenUpdating = True
End Sub

mal
03-18-2015, 09:13 AM
I tried it but it did not work. Here is my original code generated by trying to write a macro as described before.
Sub Remove()
'
' Remove Macro
' Remove Japan Stk, Volatile and Trading- rows
'
' Keyboard Shortcut: Ctrl+t
'
Range("C3").Select
ActiveWindow.LargeScroll Down:=38
Range("P1675:P1677").Select
Range("P1677").Activate
Application.FindFormat.Clear
Application.FindFormat.NumberFormat = "General"
WithApplication.FindFormat
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText =False
.Orientation =0
.AddIndent =False
.ShrinkToFit =False
.MergeCells =False
End With
WithApplication.FindFormat.Font
.Name ="Calibri"
.FontStyle ="Regular"
.Size = 11
.Strikethrough= False
.Superscript =False
.Subscript =False
.Underline =xlUnderlineStyleNone
.ThemeColor =2
.TintAndShade= 0
.ThemeFont =xlThemeFontMinor
End With
Application.FindFormat.Borders(xlLeft).LineStyle = xlNone
Application.FindFormat.Borders(xlRight).LineStyle = xlNone
Application.FindFormat.Borders(xlBottom).LineStyle= xlNone
Application.FindFormat.Borders(xlDiagonalDown).LineStyle = xlNone
Application.FindFormat.Borders(xlDiagonalUp).LineStyle = xlNone
WithApplication.FindFormat.Interior
.Pattern =xlNone
.TintAndShade= 0
.PatternTintAndShade = 0
End With
Application.FindFormat.Locked = True
Application.FindFormat.FormulaHidden = False
Range("P1672").Select
ActiveCell.FormulaR1C1 = ""
Selection.EntireRow.Delete
ActiveWindow.ScrollRow = 1618
ActiveWindow.ScrollRow = 1615
ActiveWindow.ScrollRow = 1613
ActiveWindow.ScrollRow = 1610
ActiveWindow.ScrollRow = 1608
ActiveWindow.ScrollRow = 1605
ActiveWindow.ScrollRow= 1603
ActiveWindow.ScrollRow = 1600
ActiveWindow.ScrollRow = 1595
ActiveWindow.ScrollRow = 1590
ActiveWindow.ScrollRow = 1589
ActiveWindow.ScrollRow = 1585
ActiveWindow.ScrollRow = 1584
ActiveWindow.ScrollRow = 1579
ActiveWindow.ScrollRow= 1575
ActiveWindow.ScrollRow = 1570
ActiveWindow.ScrollRow = 1569
ActiveWindow.ScrollRow = 1565
ActiveWindow.ScrollRow = 1559
ActiveWindow.ScrollRow = 1554
ActiveWindow.ScrollRow = 1549
ActiveWindow.ScrollRow = 1546
ActiveWindow.ScrollRow = 1541
ActiveWindow.ScrollRow = 1529
ActiveWindow.ScrollRow = 1526
ActiveWindow.ScrollRow = 1521
ActiveWindow.ScrollRow = 1516
ActiveWindow.ScrollRow = 1514
ActiveWindow.ScrollRow = 1511
ActiveWindow.ScrollRow = 1509
ActiveWindow.ScrollRow = 1504
ActiveWindow.ScrollRow = 1499
ActiveWindow.ScrollRow = 1496
ActiveWindow.ScrollRow = 1494
ActiveWindow.ScrollRow = 1491
ActiveWindow.ScrollRow = 1489
ActiveWindow.ScrollRow= 1486
ActiveWindow.ScrollRow = 1484
ActiveWindow.ScrollRow = 1481
ActiveWindow.ScrollRow = 1479
ActiveWindow.ScrollRow = 1476
ActiveWindow.ScrollRow = 1474
ActiveWindow.ScrollRow = 1471
ActiveWindow.ScrollRow = 1470
ActiveWindow.ScrollRow = 1466
ActiveWindow.ScrollRow = 1465
ActiveWindow.ScrollRow = 1461
ActiveWindow.ScrollRow = 1456
ActiveWindow.ScrollRow = 1455
ActiveWindow.ScrollRow = 1451
ActiveWindow.ScrollRow = 1450
ActiveWindow.ScrollRow= 1446
ActiveWindow.ScrollRow = 1445
ActiveWindow.ScrollRow = 1441
ActiveWindow.ScrollRow = 1430
ActiveWindow.ScrollRow = 1425
ActiveWindow.ScrollRow = 1422
ActiveWindow.ScrollRow = 1420
ActiveWindow.ScrollRow = 1417
ActiveWindow.ScrollRow = 1412
ActiveWindow.ScrollRow = 1410
ActiveWindow.ScrollRow = 1407
ActiveWindow.ScrollRow = 1405
ActiveWindow.ScrollRow = 1402
ActiveWindow.ScrollRow = 1397
ActiveWindow.ScrollRow = 1395
ActiveWindow.ScrollRow= 1392
ActiveWindow.ScrollRow = 1390
ActiveWindow.ScrollRow = 1385
ActiveWindow.ScrollRow = 1380
ActiveWindow.ScrollRow = 1377
ActiveWindow.ScrollRow = 1375
ActiveWindow.ScrollRow = 1370
ActiveWindow.ScrollRow = 1367
ActiveWindow.ScrollRow = 1365
ActiveWindow.ScrollRow = 1362
ActiveWindow.ScrollRow = 1360
ActiveWindow.ScrollRow = 1357
ActiveWindow.ScrollRow = 1355
ActiveWindow.ScrollRow = 1352
ActiveWindow.ScrollRow = 1350
ActiveWindow.ScrollRow= 1347
ActiveWindow.ScrollRow = 1345
ActiveWindow.ScrollRow = 1342
ActiveWindow.ScrollRow = 1341
ActiveWindow.ScrollRow = 1337
ActiveWindow.ScrollRow = 1336
ActiveWindow.ScrollRow = 1332
ActiveWindow.ScrollRow = 1331
ActiveWindow.ScrollRow = 1327
ActiveWindow.ScrollRow = 1326
ActiveWindow.ScrollRow = 1322
ActiveWindow.ScrollRow = 1321
ActiveWindow.ScrollRow = 1317
ActiveWindow.ScrollRow = 1316
ActiveWindow.ScrollRow = 1312
ActiveWindow.ScrollRow= 1311
ActiveWindow.ScrollRow = 1307
ActiveWindow.ScrollRow = 1306
ActiveWindow.ScrollRow = 1302
ActiveWindow.ScrollRow = 1301
ActiveWindow.ScrollRow = 1298
ActiveWindow.ScrollRow = 1293
ActiveWindow.ScrollRow = 1291
ActiveWindow.ScrollRow = 1288
ActiveWindow.ScrollRow = 1286
ActiveWindow.ScrollRow = 1283
ActiveWindow.ScrollRow = 1281
ActiveWindow.ScrollRow = 1276
ActiveWindow.ScrollRow = 1271
ActiveWindow.ScrollRow = 1268
ActiveWindow.ScrollRow= 1261
ActiveWindow.ScrollRow = 1258
ActiveWindow.ScrollRow = 1248
ActiveWindow.ScrollRow = 1243
ActiveWindow.ScrollRow = 1241
ActiveWindow.ScrollRow = 1236
ActiveWindow.ScrollRow = 1233
ActiveWindow.ScrollRow = 1223
ActiveWindow.ScrollRow = 1221
ActiveWindow.ScrollRow = 1218
ActiveWindow.ScrollRow = 1213
ActiveWindow.ScrollRow = 1212
ActiveWindow.ScrollRow = 1207
ActiveWindow.ScrollRow = 1203
ActiveWindow.ScrollRow = 1202
ActiveWindow.ScrollRow= 1198
ActiveWindow.ScrollRow = 1197
ActiveWindow.ScrollRow = 1193
ActiveWindow.ScrollRow = 1192
ActiveWindow.ScrollRow = 1188
ActiveWindow.ScrollRow = 1187
ActiveWindow.ScrollRow = 1183
ActiveWindow.ScrollRow = 1182
ActiveWindow.ScrollRow = 1178
ActiveWindow.ScrollRow = 1177
ActiveWindow.ScrollRow = 1173
ActiveWindow.ScrollRow = 1172
ActiveWindow.ScrollRow = 1169
ActiveWindow.ScrollRow = 1167
Range("C1173").Select
Application.FindFormat.Clear
Application.FindFormat.NumberFormat = "General"
WithApplication.FindFormat
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText =False
.Orientation =0
.AddIndent =False
.ShrinkToFit =False
.MergeCells =False
End With
WithApplication.FindFormat.Font
.Name ="Calibri"
.FontStyle ="Regular"
.Size = 11
.Strikethrough= False
.Superscript =False
.Subscript =False
.Underline =xlUnderlineStyleNone
.ThemeColor =2
.TintAndShade= 0
.ThemeFont =xlThemeFontMinor
End With
Application.FindFormat.Borders(xlLeft).LineStyle = xlNone
Application.FindFormat.Borders(xlRight).LineStyle = xlNone
Application.FindFormat.Borders(xlBottom).LineStyle = xlNone
Application.FindFormat.Borders(xlDiagonalDown).LineStyle = xlNone
Application.FindFormat.Borders(xlDiagonalUp).LineStyle = xlNone
WithApplication.FindFormat.Interior
.Pattern =xlNone
.TintAndShade= 0
.PatternTintAndShade = 0
End With
Application.FindFormat.Locked = True
Application.FindFormat.FormulaHidden = False
Selection.EntireRow.Delete
ActiveWindow.ScrollRow = 1169
ActiveWindow.ScrollRow = 1172
ActiveWindow.ScrollRow = 1173
ActiveWindow.ScrollRow = 1177
ActiveWindow.ScrollRow = 1182
ActiveWindow.ScrollRow = 1183
ActiveWindow.ScrollRow = 1187
ActiveWindow.ScrollRow= 1188
ActiveWindow.ScrollRow = 1192
ActiveWindow.ScrollRow = 1193
ActiveWindow.ScrollRow = 1197
ActiveWindow.ScrollRow = 1198
ActiveWindow.ScrollRow = 1202
ActiveWindow.ScrollRow = 1203
ActiveWindow.ScrollRow = 1207
ActiveWindow.ScrollRow = 1212
ActiveWindow.ScrollRow = 1213
ActiveWindow.ScrollRow = 1216
ActiveWindow.ScrollRow = 1218
ActiveWindow.ScrollRow = 1221
ActiveWindow.ScrollRow = 1223
ActiveWindow.ScrollRow = 1226
ActiveWindow.ScrollRow= 1228
ActiveWindow.ScrollRow = 1231
ActiveWindow.ScrollRow = 1233
ActiveWindow.ScrollRow = 1236
ActiveWindow.ScrollRow = 1238
ActiveWindow.ScrollRow = 1241
ActiveWindow.ScrollRow = 1243
ActiveWindow.ScrollRow = 1246
ActiveWindow.ScrollRow = 1248
ActiveWindow.ScrollRow = 1251
ActiveWindow.ScrollRow = 1253
ActiveWindow.ScrollRow = 1256
ActiveWindow.ScrollRow = 1258
ActiveWindow.ScrollRow = 1261
ActiveWindow.ScrollRow = 1263
ActiveWindow.ScrollRow= 1266
ActiveWindow.ScrollRow = 1268
ActiveWindow.ScrollRow = 1271
ActiveWindow.ScrollRow = 1273
ActiveWindow.ScrollRow = 1276
ActiveWindow.ScrollRow = 1278
ActiveWindow.ScrollRow = 1281
ActiveWindow.ScrollRow = 1283
ActiveWindow.ScrollRow = 1286
ActiveWindow.ScrollRow = 1288
ActiveWindow.ScrollRow = 1291
ActiveWindow.ScrollRow = 1293
ActiveWindow.ScrollRow = 1296
ActiveWindow.ScrollRow = 1298
ActiveWindow.ScrollRow = 1301
ActiveWindow.ScrollRow= 1302
ActiveWindow.ScrollRow = 1306
ActiveWindow.ScrollRow = 1307
ActiveWindow.ScrollRow = 1311
ActiveWindow.ScrollRow = 1312
ActiveWindow.ScrollRow = 1316
ActiveWindow.ScrollRow = 1317
ActiveWindow.ScrollRow = 1321
ActiveWindow.ScrollRow = 1322
ActiveWindow.ScrollRow = 1326
ActiveWindow.ScrollRow = 1327
ActiveWindow.ScrollRow = 1331
Range("C1347").Select
Application.FindFormat.Clear
Application.FindFormat.NumberFormat = "General"
With Application.FindFormat
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText =False
.Orientation =0
.AddIndent =False
.ShrinkToFit =False
.MergeCells =False
End With
WithApplication.FindFormat.Font
.Name ="Calibri"
.FontStyle ="Regular"
.Size = 11
.Strikethrough= False
.Superscript =False
.Subscript =False
.Underline =xlUnderlineStyleNone
.ThemeColor =2
.TintAndShade= 0
.ThemeFont =xlThemeFontMinor
End With
Application.FindFormat.Borders(xlLeft).LineStyle = xlNone
Application.FindFormat.Borders(xlRight).LineStyle = xlNone
Application.FindFormat.Borders(xlBottom).LineStyle = xlNone
Application.FindFormat.Borders(xlDiagonalDown).LineStyle = xlNone
Application.FindFormat.Borders(xlDiagonalUp).LineStyle = xlNone
WithApplication.FindFormat.Interior
.Pattern =xlNone
.TintAndShade= 0
.PatternTintAndShade = 0
End With
Application.FindFormat.Locked = True
Application.FindFormat.FormulaHidden = False
Selection.EntireRow.Delete
End Sub

mal
03-18-2015, 09:21 AM
Here is my VBA using your code.
Sub Remover()
' Remover Macro
' Removes Japan Stk, Volatile and Trading-
' Keyboard Shortcut: Ctrl+r
Range("B1").Select
Selection.EntireColumn.Delete
Selection.EntireRow.Delete
ActiveWindow.ScrollRow = 1154
ActiveWindow.ScrollRow = 1157
ActiveWindow.ScrollRow = 1159
ActiveWindow.ScrollRow = 1162
ActiveWindow.ScrollRow = 1164
ActiveWindow.ScrollRow = 1167
ActiveWindow.ScrollRow = 1169
ActiveWindow.ScrollRow = 1172
ActiveWindow.ScrollRow = 1174
ActiveWindow.ScrollRow = 1177
ActiveWindow.ScrollRow = 1179
ActiveWindow.ScrollRow = 1182
ActiveWindow.ScrollRow = 1180
ActiveWindow.ScrollRow = 1182
ActiveWindow.ScrollRow = 1184
ActiveWindow.ScrollRow = 1187
ActiveWindow.ScrollRow = 1189
ActiveWindow.ScrollRow = 1192
ActiveWindow.ScrollRow = 1194
ActiveWindow.ScrollRow = 1197
ActiveWindow.ScrollRow = 1199
ActiveWindow.ScrollRow = 1202
ActiveWindow.ScrollRow = 1203
ActiveWindow.ScrollRow = 1207
ActiveWindow.ScrollRow = 1208
ActiveWindow.ScrollRow = 1212
ActiveWindow.ScrollRow = 1213
ActiveWindow.ScrollRow = 1217
ActiveWindow.ScrollRow = 1218
ActiveWindow.ScrollRow = 1222
ActiveWindow.ScrollRow = 1223
ActiveWindow.ScrollRow = 1227
ActiveWindow.ScrollRow = 1228
ActiveWindow.ScrollRow = 1232
ActiveWindow.ScrollRow = 1233
ActiveWindow.ScrollRow = 1237
ActiveWindow.ScrollRow = 1238
ActiveWindow.ScrollRow = 1241
ActiveWindow.ScrollRow = 1243
ActiveWindow.ScrollRow = 1246
ActiveWindow.ScrollRow = 1248
ActiveWindow.ScrollRow = 1251
ActiveWindow.ScrollRow = 1253
ActiveWindow.ScrollRow = 1256
ActiveWindow.ScrollRow = 1258
ActiveWindow.ScrollRow = 1261
ActiveWindow.ScrollRow = 1263
ActiveWindow.ScrollRow = 1266
ActiveWindow.ScrollRow = 1268
ActiveWindow.ScrollRow = 1271
ActiveWindow.ScrollRow = 1273
ActiveWindow.ScrollRow = 1276
ActiveWindow.ScrollRow = 1278
ActiveWindow.ScrollRow = 1281
ActiveWindow.ScrollRow = 1283
ActiveWindow.ScrollRow = 1286
ActiveWindow.ScrollRow = 1288
ActiveWindow.ScrollRow = 1291
ActiveWindow.ScrollRow = 1293
ActiveWindow.ScrollRow = 1296
ActiveWindow.ScrollRow = 1294
ActiveWindow.ScrollRow = 1291
ActiveWindow.ScrollRow = 1289
ActiveWindow.ScrollRow = 1284
ActiveWindow.ScrollRow = 1281
ActiveWindow.ScrollRow = 1271
ActiveWindow.ScrollRow = 1266
ActiveWindow.ScrollRow = 1256
ActiveWindow.ScrollRow = 1250
ActiveWindow.ScrollRow = 1237
ActiveWindow.ScrollRow = 1232
ActiveWindow.ScrollRow = 1227
ActiveWindow.ScrollRow = 1217
ActiveWindow.ScrollRow = 1205
ActiveWindow.ScrollRow = 1200
ActiveWindow.ScrollRow = 1197
ActiveWindow.ScrollRow = 1195
ActiveWindow.ScrollRow = 1185
ActiveWindow.ScrollRow = 1170
ActiveWindow.ScrollRow = 1157
ActiveWindow.ScrollRow = 1151
ActiveWindow.ScrollRow = 1146
ActiveWindow.ScrollRow = 1142
ActiveWindow.ScrollRow = 1137
ActiveWindow.ScrollRow = 1126
ActiveWindow.ScrollRow = 1121
ActiveWindow.ScrollRow = 1116
ActiveWindow.ScrollRow = 1113
ActiveWindow.ScrollRow = 1108
ActiveWindow.ScrollRow = 1106
ActiveWindow.ScrollRow = 1098
ActiveWindow.ScrollRow = 1096
ActiveWindow.ScrollRow = 1093
ActiveWindow.ScrollRow = 1088
ActiveWindow.ScrollRow = 1086
ActiveWindow.ScrollRow = 1083
ActiveWindow.ScrollRow = 1081
ActiveWindow.ScrollRow = 1078
ActiveWindow.ScrollRow = 1071
ActiveWindow.ScrollRow = 1066
ActiveWindow.ScrollRow = 1061
ActiveWindow.ScrollRow = 1058
ActiveWindow.ScrollRow = 1056
ActiveWindow.ScrollRow = 1052
ActiveWindow.ScrollRow = 1048
ActiveWindow.ScrollRow = 1047
ActiveWindow.ScrollRow = 1043
ActiveWindow.ScrollRow = 1038
ActiveWindow.ScrollRow = 1037
ActiveWindow.ScrollRow = 1033
ActiveWindow.ScrollRow = 1032
ActiveWindow.ScrollRow = 1028
ActiveWindow.ScrollRow = 1027
ActiveWindow.ScrollRow = 1023
ActiveWindow.ScrollRow = 1017
ActiveWindow.ScrollRow = 1012
ActiveWindow.ScrollRow = 1007
ActiveWindow.ScrollRow = 1002
ActiveWindow.ScrollRow = 999
ActiveWindow.ScrollRow = 994
ActiveWindow.ScrollRow = 987
ActiveWindow.ScrollRow = 984
ActiveWindow.ScrollRow = 969
ActiveWindow.ScrollRow = 964
ActiveWindow.ScrollRow = 959
ActiveWindow.ScrollRow = 954
ActiveWindow.ScrollRow = 947
ActiveWindow.ScrollRow = 938
ActiveWindow.ScrollRow = 933
ActiveWindow.ScrollRow = 918
ActiveWindow.ScrollRow = 913
ActiveWindow.ScrollRow = 905
ActiveWindow.ScrollRow = 898
ActiveWindow.ScrollRow = 880
ActiveWindow.ScrollRow = 873
ActiveWindow.ScrollRow = 843
ActiveWindow.ScrollRow = 829
ActiveWindow.ScrollRow = 814
ActiveWindow.ScrollRow = 805
ActiveWindow.ScrollRow = 781
ActiveWindow.ScrollRow = 771
ActiveWindow.ScrollRow = 766
ActiveWindow.ScrollRow = 761
ActiveWindow.ScrollRow = 754
ActiveWindow.ScrollRow = 749
ActiveWindow.ScrollRow = 746
ActiveWindow.ScrollRow = 741
ActiveWindow.ScrollRow = 726
ActiveWindow.ScrollRow = 721
ActiveWindow.ScrollRow = 716
ActiveWindow.ScrollRow = 711
ActiveWindow.ScrollRow = 706
ActiveWindow.ScrollRow = 701
ActiveWindow.ScrollRow = 696
ActiveWindow.ScrollRow = 691
ActiveWindow.ScrollRow = 682
ActiveWindow.ScrollRow = 677
ActiveWindow.ScrollRow = 672
ActiveWindow.ScrollRow = 670
ActiveWindow.ScrollRow = 665
ActiveWindow.ScrollRow = 662
ActiveWindow.ScrollRow = 657
ActiveWindow.ScrollRow = 650
ActiveWindow.ScrollRow = 645
ActiveWindow.ScrollRow = 642
ActiveWindow.ScrollRow = 640
ActiveWindow.ScrollRow = 632
ActiveWindow.ScrollRow = 627
ActiveWindow.ScrollRow = 615
ActiveWindow.ScrollRow = 612
ActiveWindow.ScrollRow = 607
ActiveWindow.ScrollRow = 606
ActiveWindow.ScrollRow = 602
ActiveWindow.ScrollRow = 601
ActiveWindow.ScrollRow = 596
ActiveWindow.ScrollRow = 591
ActiveWindow.ScrollRow = 587
ActiveWindow.ScrollRow = 586
ActiveWindow.ScrollRow = 582
ActiveWindow.ScrollRow = 581
ActiveWindow.ScrollRow = 577
ActiveWindow.ScrollRow = 576
ActiveWindow.ScrollRow = 573
ActiveWindow.ScrollRow = 566
ActiveWindow.ScrollRow = 563
ActiveWindow.ScrollRow = 558
ActiveWindow.ScrollRow = 556
ActiveWindow.ScrollRow = 553
ActiveWindow.ScrollRow = 548
ActiveWindow.ScrollRow = 533
ActiveWindow.ScrollRow = 526
ActiveWindow.ScrollRow = 523
ActiveWindow.ScrollRow = 518
ActiveWindow.ScrollRow = 513
ActiveWindow.ScrollRow = 508
ActiveWindow.ScrollRow = 503
ActiveWindow.ScrollRow = 498
ActiveWindow.ScrollRow = 487
ActiveWindow.ScrollRow = 483
ActiveWindow.ScrollRow = 482
ActiveWindow.ScrollRow = 477
ActiveWindow.ScrollRow = 472
ActiveWindow.ScrollRow = 468
ActiveWindow.ScrollRow = 467
ActiveWindow.ScrollRow = 463
ActiveWindow.ScrollRow = 462
ActiveWindow.ScrollRow = 459
ActiveWindow.ScrollRow = 457
ActiveWindow.ScrollRow = 454
ActiveWindow.ScrollRow = 452
ActiveWindow.ScrollRow = 449
ActiveWindow.ScrollRow = 447
ActiveWindow.ScrollRow = 444
ActiveWindow.ScrollRow = 442
ActiveWindow.ScrollRow = 439
ActiveWindow.ScrollRow = 437
ActiveWindow.ScrollRow = 434
ActiveWindow.ScrollRow = 427
ActiveWindow.ScrollRow = 424
ActiveWindow.ScrollRow = 419
ActiveWindow.ScrollRow = 417
ActiveWindow.ScrollRow = 414
ActiveWindow.ScrollRow = 409
ActiveWindow.ScrollRow = 407
ActiveWindow.ScrollRow = 404
ActiveWindow.ScrollRow = 402
ActiveWindow.ScrollRow = 399
ActiveWindow.ScrollRow = 392
ActiveWindow.ScrollRow = 389
ActiveWindow.ScrollRow = 388
ActiveWindow.ScrollRow = 384
ActiveWindow.ScrollRow = 383
ActiveWindow.ScrollRow = 378
ActiveWindow.ScrollRow = 374
ActiveWindow.ScrollRow = 373
ActiveWindow.ScrollRow = 368
ActiveWindow.ScrollRow = 364
ActiveWindow.ScrollRow = 363
ActiveWindow.ScrollRow = 359
ActiveWindow.ScrollRow = 354
ActiveWindow.ScrollRow = 353
ActiveWindow.ScrollRow = 350
ActiveWindow.ScrollRow = 348
ActiveWindow.ScrollRow = 345
ActiveWindow.ScrollRow = 340
ActiveWindow.ScrollRow = 338
ActiveWindow.ScrollRow = 335
ActiveWindow.ScrollRow = 333
ActiveWindow.ScrollRow = 330
ActiveWindow.ScrollRow = 328
ActiveWindow.ScrollRow = 325
ActiveWindow.ScrollRow = 323
ActiveWindow.ScrollRow = 320
ActiveWindow.ScrollRow = 318
ActiveWindow.ScrollRow = 315
ActiveWindow.ScrollRow = 313
ActiveWindow.ScrollRow = 310
ActiveWindow.ScrollRow = 308
ActiveWindow.ScrollRow = 305
ActiveWindow.ScrollRow = 303
ActiveWindow.ScrollRow = 300
ActiveWindow.ScrollRow = 298
ActiveWindow.ScrollRow = 293
ActiveWindow.ScrollRow = 290
ActiveWindow.ScrollRow = 285
ActiveWindow.ScrollRow = 283
ActiveWindow.ScrollRow = 278
ActiveWindow.ScrollRow = 275
ActiveWindow.ScrollRow = 274
ActiveWindow.ScrollRow = 264
ActiveWindow.ScrollRow = 260
ActiveWindow.ScrollRow = 255
ActiveWindow.ScrollRow = 250
ActiveWindow.ScrollRow = 249
ActiveWindow.ScrollRow = 245
ActiveWindow.ScrollRow = 244
ActiveWindow.ScrollRow = 241
ActiveWindow.ScrollRow = 239
ActiveWindow.ScrollRow = 236
ActiveWindow.ScrollRow = 234
ActiveWindow.ScrollRow = 231
ActiveWindow.ScrollRow = 229
ActiveWindow.ScrollRow = 226
ActiveWindow.ScrollRow = 224
ActiveWindow.ScrollRow = 219
ActiveWindow.ScrollRow = 216
ActiveWindow.ScrollRow = 214
ActiveWindow.ScrollRow = 211
ActiveWindow.ScrollRow = 209
ActiveWindow.ScrollRow = 206
ActiveWindow.ScrollRow = 204
ActiveWindow.ScrollRow = 201
ActiveWindow.ScrollRow = 199
ActiveWindow.ScrollRow = 196
ActiveWindow.ScrollRow = 194
ActiveWindow.ScrollRow = 191
ActiveWindow.ScrollRow = 189
ActiveWindow.ScrollRow = 186
ActiveWindow.ScrollRow = 184
ActiveWindow.ScrollRow = 181
ActiveWindow.ScrollRow = 179
ActiveWindow.ScrollRow = 176
ActiveWindow.ScrollRow = 174
ActiveWindow.ScrollRow = 171
ActiveWindow.ScrollRow = 165
ActiveWindow.ScrollRow = 161
ActiveWindow.ScrollRow = 160
ActiveWindow.ScrollRow = 156
ActiveWindow.ScrollRow = 155
ActiveWindow.ScrollRow = 151
ActiveWindow.ScrollRow = 150
ActiveWindow.ScrollRow = 146
ActiveWindow.ScrollRow = 145
ActiveWindow.ScrollRow = 140
ActiveWindow.ScrollRow = 136
ActiveWindow.ScrollRow = 135
ActiveWindow.ScrollRow = 131
ActiveWindow.ScrollRow = 130
ActiveWindow.ScrollRow = 127
ActiveWindow.ScrollRow = 125
ActiveWindow.ScrollRow = 123
ActiveWindow.ScrollRow = 120
ActiveWindow.ScrollRow = 118
ActiveWindow.ScrollRow = 113
ActiveWindow.ScrollRow = 110
ActiveWindow.ScrollRow = 105
ActiveWindow.ScrollRow = 98
ActiveWindow.ScrollRow = 93
ActiveWindow.ScrollRow = 74
ActiveWindow.ScrollRow = 64
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 1
End Sub

Sub Remove()
'Remove Macro
' Remove Japan Stk, Volatile and Trading- rows
'Keyboard Shortcut: Ctrl+t
'
Range("C3").Select
ActiveWindow.LargeScroll Down:=38
Range("P1675:P1677").Select
Range("P1677").Activate
Application.FindFormat.Clear
Application.FindFormat.NumberFormat = "General"
With Application.FindFormat
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Application.FindFormat.Font
.Name = "Calibri"
.FontStyle = "Regular"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.Underline = xlUnderlineStyleNone
.ThemeColor = 2
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Application.FindFormat.Borders(xlLeft).LineStyle = xlNone
Application.FindFormat.Borders(xlRight).LineStyle = xlNone
Application.FindFormat.Borders(xlBottom).LineStyle = xlNone
Application.FindFormat.Borders(xlDiagonalDown).LineStyle = xlNone
Application.FindFormat.Borders(xlDiagonalUp).LineStyle = xlNone
With Application.FindFormat.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Application.FindFormat.Locked = True
Application.FindFormat.FormulaHidden = False
Range("P1672").Select
ActiveCell.FormulaR1C1 = ""
Selection.EntireRow.Delete
ActiveWindow.ScrollRow = 1618
ActiveWindow.ScrollRow = 1615
ActiveWindow.ScrollRow = 1613
ActiveWindow.ScrollRow = 1610
ActiveWindow.ScrollRow = 1608
ActiveWindow.ScrollRow = 1605
ActiveWindow.ScrollRow = 1603
ActiveWindow.ScrollRow = 1600
ActiveWindow.ScrollRow = 1595
ActiveWindow.ScrollRow = 1590
ActiveWindow.ScrollRow = 1589
ActiveWindow.ScrollRow = 1585
ActiveWindow.ScrollRow = 1584
ActiveWindow.ScrollRow = 1579
ActiveWindow.ScrollRow = 1575
ActiveWindow.ScrollRow = 1570
ActiveWindow.ScrollRow = 1569
ActiveWindow.ScrollRow = 1565
ActiveWindow.ScrollRow = 1559
ActiveWindow.ScrollRow = 1554
ActiveWindow.ScrollRow = 1549
ActiveWindow.ScrollRow = 1546
ActiveWindow.ScrollRow = 1541
ActiveWindow.ScrollRow = 1529
ActiveWindow.ScrollRow = 1526
ActiveWindow.ScrollRow = 1521
ActiveWindow.ScrollRow = 1516
ActiveWindow.ScrollRow = 1514
ActiveWindow.ScrollRow = 1511
ActiveWindow.ScrollRow = 1509
ActiveWindow.ScrollRow = 1504
ActiveWindow.ScrollRow = 1499
ActiveWindow.ScrollRow = 1496
ActiveWindow.ScrollRow = 1494
ActiveWindow.ScrollRow = 1491
ActiveWindow.ScrollRow = 1489
ActiveWindow.ScrollRow = 1486
ActiveWindow.ScrollRow = 1484
ActiveWindow.ScrollRow = 1481
ActiveWindow.ScrollRow = 1479
ActiveWindow.ScrollRow = 1476
ActiveWindow.ScrollRow = 1474
ActiveWindow.ScrollRow = 1471
ActiveWindow.ScrollRow = 1470
ActiveWindow.ScrollRow = 1466
ActiveWindow.ScrollRow = 1465
ActiveWindow.ScrollRow = 1461
ActiveWindow.ScrollRow = 1456
ActiveWindow.ScrollRow = 1455
ActiveWindow.ScrollRow = 1451
ActiveWindow.ScrollRow = 1450
ActiveWindow.ScrollRow = 1446
ActiveWindow.ScrollRow = 1445
ActiveWindow.ScrollRow = 1441
ActiveWindow.ScrollRow = 1430
ActiveWindow.ScrollRow = 1425
ActiveWindow.ScrollRow = 1422
ActiveWindow.ScrollRow = 1420
ActiveWindow.ScrollRow = 1417
ActiveWindow.ScrollRow = 1412
ActiveWindow.ScrollRow = 1410
ActiveWindow.ScrollRow = 1407
ActiveWindow.ScrollRow = 1405
ActiveWindow.ScrollRow = 1402
ActiveWindow.ScrollRow = 1397
ActiveWindow.ScrollRow = 1395
ActiveWindow.ScrollRow = 1392
ActiveWindow.ScrollRow = 1390
ActiveWindow.ScrollRow = 1385
ActiveWindow.ScrollRow = 1380
ActiveWindow.ScrollRow = 1377
ActiveWindow.ScrollRow = 1375
ActiveWindow.ScrollRow = 1370
ActiveWindow.ScrollRow = 1367
ActiveWindow.ScrollRow = 1365
ActiveWindow.ScrollRow = 1362
ActiveWindow.ScrollRow = 1360
ActiveWindow.ScrollRow = 1357
ActiveWindow.ScrollRow = 1355
ActiveWindow.ScrollRow = 1352
ActiveWindow.ScrollRow = 1350
ActiveWindow.ScrollRow = 1347
ActiveWindow.ScrollRow = 1345
ActiveWindow.ScrollRow = 1342
ActiveWindow.ScrollRow = 1341
ActiveWindow.ScrollRow = 1337
ActiveWindow.ScrollRow = 1336
ActiveWindow.ScrollRow = 1332
ActiveWindow.ScrollRow = 1331
ActiveWindow.ScrollRow = 1327
ActiveWindow.ScrollRow = 1326
ActiveWindow.ScrollRow = 1322
ActiveWindow.ScrollRow = 1321
ActiveWindow.ScrollRow = 1317
ActiveWindow.ScrollRow = 1316
ActiveWindow.ScrollRow = 1312
ActiveWindow.ScrollRow = 1311
ActiveWindow.ScrollRow = 1307
ActiveWindow.ScrollRow = 1306
ActiveWindow.ScrollRow = 1302
ActiveWindow.ScrollRow = 1301
ActiveWindow.ScrollRow = 1298
ActiveWindow.ScrollRow = 1293
ActiveWindow.ScrollRow = 1291
ActiveWindow.ScrollRow = 1288
ActiveWindow.ScrollRow = 1286
ActiveWindow.ScrollRow = 1283
ActiveWindow.ScrollRow = 1281
ActiveWindow.ScrollRow = 1276
ActiveWindow.ScrollRow = 1271
ActiveWindow.ScrollRow = 1268
ActiveWindow.ScrollRow = 1261
ActiveWindow.ScrollRow = 1258
ActiveWindow.ScrollRow = 1248
ActiveWindow.ScrollRow = 1243
ActiveWindow.ScrollRow = 1241
ActiveWindow.ScrollRow = 1236
ActiveWindow.ScrollRow = 1233
ActiveWindow.ScrollRow = 1223
ActiveWindow.ScrollRow = 1221
ActiveWindow.ScrollRow = 1218
ActiveWindow.ScrollRow = 1213
ActiveWindow.ScrollRow = 1212
ActiveWindow.ScrollRow = 1207
ActiveWindow.ScrollRow = 1203
ActiveWindow.ScrollRow = 1202
ActiveWindow.ScrollRow = 1198
ActiveWindow.ScrollRow = 1197
ActiveWindow.ScrollRow = 1193
ActiveWindow.ScrollRow = 1192
ActiveWindow.ScrollRow = 1188
ActiveWindow.ScrollRow = 1187
ActiveWindow.ScrollRow = 1183
ActiveWindow.ScrollRow = 1182
ActiveWindow.ScrollRow = 1178
ActiveWindow.ScrollRow = 1177
ActiveWindow.ScrollRow = 1173
ActiveWindow.ScrollRow = 1172
ActiveWindow.ScrollRow = 1169
ActiveWindow.ScrollRow = 1167
Range("C1173").Select
Application.FindFormat.Clear
Application.FindFormat.NumberFormat = "General"
With Application.FindFormat
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Application.FindFormat.Font
.Name = "Calibri"
.FontStyle = "Regular"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.Underline = xlUnderlineStyleNone
.ThemeColor = 2
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Application.FindFormat.Borders(xlLeft).LineStyle = xlNone
Application.FindFormat.Borders(xlRight).LineStyle = xlNone
Application.FindFormat.Borders(xlBottom).LineStyle = xlNone
Application.FindFormat.Borders(xlDiagonalDown).LineStyle = xlNone
Application.FindFormat.Borders(xlDiagonalUp).LineStyle = xlNone
With Application.FindFormat.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Application.FindFormat.Locked = True
Application.FindFormat.FormulaHidden = False
Selection.EntireRow.Delete
ActiveWindow.ScrollRow = 1169
ActiveWindow.ScrollRow = 1172
ActiveWindow.ScrollRow = 1173
ActiveWindow.ScrollRow = 1177
ActiveWindow.ScrollRow = 1182
ActiveWindow.ScrollRow = 1183
ActiveWindow.ScrollRow = 1187
ActiveWindow.ScrollRow = 1188
ActiveWindow.ScrollRow = 1192
ActiveWindow.ScrollRow = 1193
ActiveWindow.ScrollRow = 1197
ActiveWindow.ScrollRow = 1198
ActiveWindow.ScrollRow = 1202
ActiveWindow.ScrollRow = 1203
ActiveWindow.ScrollRow = 1207
ActiveWindow.ScrollRow = 1212
ActiveWindow.ScrollRow = 1213
ActiveWindow.ScrollRow = 1216
ActiveWindow.ScrollRow = 1218
ActiveWindow.ScrollRow = 1221
ActiveWindow.ScrollRow = 1223
ActiveWindow.ScrollRow = 1226
ActiveWindow.ScrollRow = 1228
ActiveWindow.ScrollRow = 1231
ActiveWindow.ScrollRow = 1233
ActiveWindow.ScrollRow = 1236
ActiveWindow.ScrollRow = 1238
ActiveWindow.ScrollRow = 1241
ActiveWindow.ScrollRow = 1243
ActiveWindow.ScrollRow = 1246
ActiveWindow.ScrollRow = 1248
ActiveWindow.ScrollRow = 1251
ActiveWindow.ScrollRow = 1253
ActiveWindow.ScrollRow = 1256
ActiveWindow.ScrollRow = 1258
ActiveWindow.ScrollRow = 1261
ActiveWindow.ScrollRow = 1263
ActiveWindow.ScrollRow = 1266
ActiveWindow.ScrollRow = 1268
ActiveWindow.ScrollRow = 1271
ActiveWindow.ScrollRow = 1273
ActiveWindow.ScrollRow = 1276
ActiveWindow.ScrollRow = 1278
ActiveWindow.ScrollRow = 1281
ActiveWindow.ScrollRow = 1283
ActiveWindow.ScrollRow = 1286
ActiveWindow.ScrollRow = 1288
ActiveWindow.ScrollRow = 1291
ActiveWindow.ScrollRow = 1293
ActiveWindow.ScrollRow = 1296
ActiveWindow.ScrollRow = 1298
ActiveWindow.ScrollRow = 1301
ActiveWindow.ScrollRow = 1302
ActiveWindow.ScrollRow = 1306
ActiveWindow.ScrollRow = 1307
ActiveWindow.ScrollRow = 1311
ActiveWindow.ScrollRow = 1312
ActiveWindow.ScrollRow = 1316
ActiveWindow.ScrollRow = 1317
ActiveWindow.ScrollRow = 1321
ActiveWindow.ScrollRow = 1322
ActiveWindow.ScrollRow = 1326
ActiveWindow.ScrollRow = 1327
ActiveWindow.ScrollRow = 1331
Range("C1347").Select
Application.FindFormat.Clear
Application.FindFormat.NumberFormat = "General"
With Application.FindFormat
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Application.FindFormat.Font
.Name = "Calibri"
.FontStyle = "Regular"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.Underline = xlUnderlineStyleNone
.ThemeColor = 2
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Application.FindFormat.Borders(xlLeft).LineStyle = xlNone
Application.FindFormat.Borders(xlRight).LineStyle = xlNone
Application.FindFormat.Borders(xlBottom).LineStyle = xlNone
Application.FindFormat.Borders(xlDiagonalDown).LineStyle = xlNone
Application.FindFormat.Borders(xlDiagonalUp).LineStyle = xlNone
With Application.FindFormat.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Application.FindFormat.Locked = True
Application.FindFormat.FormulaHidden = False
Selection.EntireRow.Delete
End Sub

jolivanes
03-18-2015, 10:49 AM
Could you describe in words what you want to do.
You said in Post #1 that you want to delete rows with "category x" and others.
Let us know in which column these "categories" are. Is that spelled with small letters?
Do you have headers in Row 1?
etc etc
You could attach a workbook with a before and after.
Sanitize it before attaching.
It does not have to be the whole file but it should be a representative copy of the original.

mal
03-18-2015, 06:41 PM
First of all, let me thank you for going to all of thistrouble for me.
Attached is the spread sheet. It contains about 1700 rows. If it does not get to you I will resend itwith some rows removed. What I am trying to do with a macro is to remove allrows that contain categories (Column C): “Japan Stk” “Volatility”and “Trading-“. Although this is not as important, I wouldalso like to remove all rows that have “#VALUE!” in the Mo column.

Thanks again, Mal



Name
Category
Style
YTD
1 mo
3 mo
1 yr
3 yr
Trading





Box
Return %
Return %
Return %
Return %
Return %
Volume
Mo














WBI SMID Tactical Growth ETF (WBIA)
Aggressive Allocation

-0.8
-2.69
0.13
---
---
36,575
-7.94


YieldShares High Income ETF (YYY)
Aggressive Allocation

-0.8
-0.9
-1.35
-1.82
---
45,082
-4.05


SPDR® MSCI Taiwan Quality Mix ETF (QTWN)
China Region

5.59
0
8.32
---
---
379
8.32


Deutsche X-trackers MSCI All China Eq (CN)
China Region

0.46
0.52
6.98
---
---
3,650
8.54


Global X China Materials ETF (CHIM)
China Region

1.4
2.99
1.82
13.66
-7.2
5,947
10.79


PowerShares China A-Share ETF (CHNA)
China Region
---
-7.35
0
13.73
71.19
---
9,761
13.73


Deutsche X-trackers Harvest CSI300 CHN A (ASHR)
China Region

-1.48
2.78
9.97
68.72
---
588,070
18.31


Market Vectors® ChinaAMC A-Share ETF (PEK)
China Region

0.59
2.78
12.41
68.45
7.85
37,229
20.75


KraneShares Bosera MSCI China A ETF (KBA)
China Region

0.33
3.39
11.08
60.71
---
11,047
21.25


Deutsche X-trackers HrvstCSI500CHN A SC (ASHS)
China Region

18.3
9.92
13.79
---
---
17,825
43.55


Market Vectors ChinaAMC SME-ChiNext ETF (CNXT)
China Region

28.67
9.38
22.34
---
---
11,534
50.48


CSOP FTSE China A50 ETF (AFTY)
China Region
---
---
---
---
---
---
19,696
#VALUE!


iPath® Pure Beta Coffee ETN (CAFE)
Commodities Agriculture
---
-23.3
-25.06
-25.54
-40.64
-18.92
14,202
-100.72


iPath® DJ-UBS Coffee SubTR ETN (JO)
Commodities Agriculture
---
-24.61
-22.91
-27.78
-43.58
-20.41
122,675
-96.51


iPath® DJ-UBS Sugar SubTR ETN (SGG)
Commodities Agriculture
---
-13.1
-15.2
-16.07
-45.12
-28.21
19,933
-61.67


iPath® DJ-UBS Softs SubTR ETN (JJS)
Commodities Agriculture
---
-13.73
-14.97
-16.4
-40.25
-20.35
4,331
-61.31


iPath® Pure Beta Sugar ETN (SGAR)
Commodities Agriculture
---
-14.57
-13.69
-18.29
-39.59
-25.78
1,850
-59.36


iPath® Pure Beta Softs ETN (GRWN)
Commodities Agriculture
---
-12.82
-11.49
-19.91
-34.99
-19.63
1,194
-54.38


RBS Rogers Enhanced Commodity ETN (RGRC)
Commodities Broad Basket
---
-6.54
-4.46
-10.99
-27.52
---
3,866
-24.37


iPath® Pure Beta S&P GSCI®-Weighted ETN (SBV)
Commodities Broad Basket
---
-7.6
0
-23.39
-34
-13.85
3,395
-23.39


Credit Suisse Commodity Rotation ETN (CSCR)
Commodities Broad Basket
---
-1.32
-2.35
-13.33
-25.18
---
5,617
-20.38


Credit Suisse Commodity Benchmark ETN (CSCB)
Commodities Broad Basket
---
-2.97
4.61
-18.46
-31.19
---
2,717
-4.63


iPath® S&P GSCI® Crude Oil TR ETN (OIL)
Commodities Energy
---
-20.02
-16.49
-26.84
-56.66
-28.14
1,376,427
-76.31


United States Oil ETF (USO)
Commodities Energy
---
-17.48
-14.37
-23.39
-52.46
-25.62
11,897,787
-66.5


ETFS Physical Swiss Gold (SGOL)
Commodities Precious Metals
---
-2.37
-5.99
-5.5
-16.1
-11.89
37,090
-23.47


Credit Suisse Gold Shares Cov Call ETN (GLDI)
Commodities Precious Metals
---
-1.05
-5.5
-3.25
-13.02
---
26,754
-19.75


AdvisorShares Gartman Gold/Yen ETF (GYEN)
Commodities Precious Metals
---
-1.91
-4.53
-4.06
-0.43
---
12,932
-17.65


ETFS Physical Palladium (PALL)
Commodities Precious Metals
---
-0.76
0.14
-2.72
1.59
3.4
54,536
-2.3


RBS Gold Trendpilot™ ETN (TBAR)
Commodities Precious Metals
---
-4.79
1.1
-4.99
-17.12
-6.67
14,445
-1.69


iShares US Consumer Services (IYC)
Consumer Cyclical

4.39
0.67
8.25
18.83
22.86
68,680
10.26


First Trust NASDAQ Global Auto ETF (CARZ)
Consumer Cyclical

8.04
1.77
8.48
7.06
14.07
15,397
13.79


Market Vectors® Retail ETF (RTH)
Consumer Cyclical

6.89
1.15
10.65
27.92
24.34
53,873
14.1


PowerShares Dynamic Retail ETF (PMR)
Consumer Cyclical

4.71
2
9.69
24.68
18.47
8,784
15.69


PowerShares Dynamic Leisure & Entmnt ETF (PEJ)
Consumer Cyclical

6.69
3.11
9.48
9.46
21.58
38,584
18.81


UBS ETRACS ISE Exclusively Hmbldrs ETN (HOMX)
Consumer Cyclical
---
---
---
---
---
---
4,500
#VALUE!


UBS ETRACS M Rst 2xLvg ISE Excly Hbs ETN (HOML)
Consumer Cyclical
---
---
---
---
---
---
13,755
#VALUE!


iShares US Consumer Goods (IYK)
Consumer Defensive

-1.12
-2.88
0.83
12.28
14.38
55,010
-7.81


Fidelity® MSCI Consumer Staples ETF (FSTA)
Consumer Defensive

-0.55
-2.5
1.39
16.53
---
87,081
-6.11


Consumer Staples Select Sector SPDR® ETF (XLP)
Consumer Defensive

-0.43
-2.46
1.5
16.36
15.66
7,241,138
-5.88


Vanguard Consumer Staples ETF (VDC)
Consumer Defensive

-0.61
-2.35
1.62
16.57
16.13
105,534
-5.43


iShares iBonds Dec 2022 Corporate (IBDN)
Corporate Bond
---
---
---
---
---
---
300
#VALUE!


iShares iBonds Dec 2023 Corporate (IBDO)
Corporate Bond
---
---
---
---
---
---
301
#VALUE!


iShares iBonds Dec 2024 Corporate (IBDP)
Corporate Bond
---
---
---
---
---
---
401
#VALUE!


iShares iBonds Dec 2025 Corporate (IBDQ)
Corporate Bond
---
---
---
---
---
---
301
#VALUE!


ALPS Emerging Sector Dividend Dogs ETF (EDOG)
Diversified Emerging Mkts
-4.14
-9.57
-5.57
---
---
5,166
-34.28


iShares MSCI Emerging Mkts Energy Capped (EMEY)
Diversified Emerging Mkts
-4
-9.46
-2.62
-16.82
-16.56
8,586
-31


SPDR® S&P Emerging Markets Dividend ETF (EDIV)
Diversified Emerging Mkts
-6.51
-8.23
-5.66
-5.22
-11.53
93,736
-30.35


iShares MSCI Frontier 100 (FM)
Diversified Emerging Mkts
-1.53
0.33
3.95
-0.05
---
333,215
4.94


JPMorgan Diversified Return EMkts Eq ETF (JPEM)
Diversified Emerging Mkts
---
-6.45
---
---
---
9,224
#VALUE!


KraneShares FTSE Emerging Mkts Plus ETF (KEMP)
Diversified Emerging Mkts
---
-4.01
---
---
---
1,251
#VALUE!


Lattice Emerging Markets Strategy ETF (ROAM)
Diversified Emerging Mkts
---
---
---
---
---
---
2,115
#VALUE!


iShares Asia/Pacific Dividend (DVYA)
Diversified Pacific/Asia

-0.58
-2.5
1.79
-2.55
3.26
6,919
-5.71


BLDRS Asia 50 ADR ETF (ADRA)
Diversified Pacific/Asia

4.05
-0.88
4.89
8.65
6.47
3,939
2.25


Vanguard FTSE Pacific ETF (VPL)
Diversified Pacific/Asia

6.42
1.59
7.64
7.55
7.56
489,034
12.41


iShares Core MSCI Pacific (IPAC)
Diversified Pacific/Asia

7.57
2.6
8.04
---
---
64,781
15.84


SPDR® Barclays Emerging Mkts Lcl Bd ETF (EBND)
Emerging Markets Bond
---
-7.78
-6.13
-7.32
-9.37
-3.89
33,097
-25.71


Market Vectors® EM Local Currency Bd ETF (EMLC)
Emerging Markets Bond
---
-7.42
-5.98
-7.23
-10.19
-5.27
465,402
-25.17


Global X MLP ETF (MLPA)
Energy Limited Partnership
-4.65
-4.29
-1.18
-0.18
---
74,597
-14.05


Yorkville High Income Infras MLP ETF (YMLI)
Energy Limited Partnership
-4.45
-4.59
-0.08
0.32
---
16,248
-13.85


Global X MLP & Energy Infrastructure ETF (MLPX)
Energy Limited Partnership
-3.64
-5.33
3.33
9.67
---
72,836
-12.66


First Trust ISE-Revere Natural Gas ETF (FCG)
Equity Energy

-9.99
-13.83
-2.3
-46.89
-18.24
774,764
-43.79


SPDR® S&P Oil & Gas Equipment&Svcs ETF (XES)
Equity Energy

-14
-12.23
-6.21
-42.8
-13.26
169,073
-42.9


Market Vectors® Gold Miners ETF (GDX)
Equity Precious Metals

-1.69
-15
-2.44
-34.15
-29.13
41,832,410
-47.44


Global X Gold Explorers ETF (GLDX)
Equity Precious Metals

-5.76
-14.51
-1.29
-42.76
-38.42
47,625
-44.82


First Trust ISE Global Platinum ETF (PLTM)
Equity Precious Metals

-7.25
-13.45
-3.47
-22.3
-22.29
9,857
-43.82


Credit Suisse FI Enhanced Europe50 ETN (FIEU)
Europe Stock
---
4.08
-6.71
0.34
-12.2
---
10,504
-19.79


First Trust STOXX® European Sel Div ETF (FDD)
Europe Stock

-0.23
-5.65
1.04
-3.4
6.24
125,262
-15.91


ETFS Diversified-Factor Developed Eurp (SBEU)
Europe Stock
---
---
-0.74
---
---
---
1,961
#VALUE!


WisdomTree Europe Hedged SmallCap Eq ETF (EUSC)
Europe Stock

---
---
---
---
---
318,236
#VALUE!


iShares MSCI Europe Financials (EUFN)
Financial

0.27
-2.26
-0.38
-8.31
9.97
233,617
-7.16


SPDR® S&P International Fincl Sect ETF (IPF)
Financial

0.1
-1.61
0.68
-1.12
9.67
6,638
-4.15


iShares Global Financials (IXG)
Financial

-0.73
-1.02
0.9
4.19
12.13
49,836
-2.16


iShares MSCI EAFE Value (EFV)
Foreign Large Value

2.29
-2.56
3.62
-2.43
7.58
240,443
-4.06


iShares Enhanced International Large-Cap (IEIL)
Foreign Large Value

2.77
-2.33
4.07
-1.19
---
21,569
-2.92


PowerShares S&P Intl Dev Hi Beta ETF (IDHB)
Foreign Large Value

4.73
0.28
2.61
-4.79
5.83
8,969
3.45


ValueShares International Quant Val ETF (IVAL)
Foreign Large Value

4.23
0.88
---
---
---
8,896
#VALUE!


Vanguard FTSE All-World ex-US Sm-Cp ETF (VSS)
Foreign Small/Mid Blend

2.19
-1.46
4.29
-5.35
5.88
108,763
-0.09


Schwab International Small-Cap Eq ETF™ (SCHC)
Foreign Small/Mid Blend

2.56
-1.6
5.23
-6.3
6.48
64,425
0.43


SPDR® Dow Jones Global Real Estate ETF (RWO)
Global Real Estate

1.91
-3.66
3.38
16.46
11.59
211,906
-7.6


Cohen & Steers Global Realty Majors ETF (GRI)
Global Real Estate

2.47
-3.67
3.49
15.68
10.39
8,431
-7.52


iShares International Developed Property (WPS)
Global Real Estate

2.98
-3.27
3.7
7.84
9.83
16,074
-6.11


iShares US Medical Devices (IHI)
Health

3.92
0.25
5.53
21.32
21.37
87,214
6.28


Health Care Select Sector SPDR® ETF (XLV)
Health

5.02
1.54
4.79
23.99
26.94
8,833,359
9.41


iShares Global Healthcare (IXJ)
Health

6.27
1.53
5.12
18.47
23.54
88,792
9.71


ARK Genomic Revolution Multi-Sector ETF (ARKG)
Health

7.7
0.28
10.61
---
---
3,666
11.45


iShares B - Ca Rated Corporate Bond (QLTC)
High Yield Bond
---
1.8
1.83
3.67
0.29
---
4,159
9.16


SPDR® Nuveen S&P High Yield Muni Bd ETF (HYMB)
High Yield Muni
---
0.25
-0.48
0.91
10.18
6.56
68,411
-0.53


Market Vectors® Short Hi-Yld Muni ETF (SHYD)
High Yield Muni
---
0.41
-0.01
1.04
3.09
---
29,360
1.01


Market Vectors® High-Yield Municipal ETF (HYD)
High Yield Muni
---
1.17
0.47
0.73
9.79
5.3
402,826
2.14


WisdomTree India Earnings ETF (EPI)
India Equity

3.72
-4.27
5
31.24
4.33
4,550,811
-7.81


iShares India 50 (INDY)
India Equity

5.18
-3.85
5.25
30.76
8.37
300,858
-6.3


iShares CMBS (CMBS)
Intermediate-Term Bond
---
1.67
0.1
1.3
3.64
3.14
14,818
1.6


Fidelity Total Bond ETF (FBND)
Intermediate-Term Bond
---
1.44
0.21
1.8
---
---
19,019
2.43


iShares Core Total USD Bond Market (IUSB)
Intermediate-Term Bond
---
1.09
0.47
1.04
---
---
39,193
2.45


PIMCO Total Return Active ETF (BOND)
Intermediate-Term Bond
---
2.26
0.3
2.04
6.53
5.96
274,003
2.94


SPDR® DoubleLine Total Return Tact ETF (TOTL)
Intermediate-Term Bond
---
---
---
---
---
---
349,950
#VALUE!



Japan Stk







0



Japan Stk







0



Japan Stk







0



Japan Stk







0



Japan Stk







0



Japan Stk







0



Japan Stk







0



Japan Stk







0



Japan Stk







0



Japan Stk







0



Japan Stk







0



Japan Stk







0



Japan Stk







0


WisdomTree Japan Hedged Real Estate ETF (DXJR)
Japan Stock

3.83
3.63
6.31
---
---
14,769
17.2


SPDR® Russell/Nomura Small Cap Japan ETF (JSC)
Japan Stock

7.86
4.05
7.82
9.39
7.77
5,820
19.97


iShares MSCI Japan Small-Cap (SCJ)
Japan Stock

8.79
3.96
9.79
11.78
9.51
18,163
21.67


Lattice US Equity Strategy ETF (ROUS)
Large Blend
---
---
---
---
---
---
656
#VALUE!


PowerShares Russell 1000 Equal Wght ETF (EQAL)
Large Blend

-0.51
-2.37
---
---
---
137,510
#VALUE!


SPDR® S&P 500 Buyback ETF (SPYB)
Large Blend

---
-1.64
---
---
---
5,515
#VALUE!


Tuttle Tactical Management US Core ETF (TUTT)
Large Blend
---
---
---
---
---
---
35,982
#VALUE!


Direxion iBillionaire Index ETF (IBLN)
Large Growth

-2.08
-3.2
0.28
---
---
39,152
-9.32


PowerShares Fundamental Pure Lg Gr ETF (PXLG)
Large Growth

-0.06
-2.77
2.67
14.02
16.11
13,586
-5.64


RBS NASDAQ-100® Trendpilot™ ETN (TNDQ)
Large Growth
---
0.63
-1.41
2
5.33
10.04
10,264
-2.23


PowerShares QQQ ETF (QQQ)
Large Growth

2.02
-1.47
2.98
19.3
18.25
36,512,380
-1.43


Global X MSCI Argentina ETF (ARGT)
Miscellaneous Region

8.63
2.2
9.02
10.19
-1.61
23,240
15.62


WisdomTree Germany Hedged Equity ETF (DXGE)
Miscellaneous Region

20.48
8
23.23
31.65
---
40,744
47.23


iShares Currency Hedged MSCI Germany (HEWG)
Miscellaneous Region

21.59
8.33
24.09
21.97
---
318,977
49.08


Deutsche X-trackers MSCI Germany Hdgd Eq (DBGR)
Miscellaneous Region

22.61
8.4
24.19
31.68
13.34
37,686
49.39


Global X MSCI Nigeria ETF (NGE)
Miscellaneous Region

-15.61
18.77
-6.68
-33.16
---
30,675
49.63


Market Vectors® Coal ETF (KOL)
Miscellaneous Sector

-11.38
-8.71
-9.13
-23.52
-25.71
138,181
-35.26


SPDR® S&P Global Infrastructure ETF (GII)
Miscellaneous Sector

-4.39
-5.06
-2.45
3.29
8.26
15,226
-17.63


Market Vectors® Uranium+Nuclear Engy ETF (NLR)
Miscellaneous Sector

-7.25
-3.36
-6.47
-4.26
-0.17
4,147
-16.55


iShares Global Infrastructure (IGF)
Miscellaneous Sector

-4.34
-4.77
-1.98
3.07
7.96
143,065
-16.29


iShares Residential Rel Est Capped (REZ)
Real Estate

2.89
-2.43
3.73
25.86
13.41
38,016
-3.56


iShares Mortgage Real Estate Capped (REM)
Real Estate

-0.34
-0.6
-0.88
4.49
7.65
978,072
-2.68


Market Vectors® Mortgage REIT Income ETF (MORT)
Real Estate

-0.63
-0.34
-1.14
5.76
9.48
37,724
-2.16


Fidelity® MSCI Real Estate ETF (FREL)
Real Estate

---
-2.78
---
---
---
25,251
#VALUE!


Deutsche X-trackers 2010 Target Date (TDD)
Retirement Income

-1.07
-1.47
-0.91
2.17
1.66
2,248
-5.32


WisdomTree Bloomberg Floating Rt Trs ETF (USFR)
Short Government
---
-0.43
-0.47
-0.27
-0.99
---
4,338
-1.68


iShares Short Treasury Bond (SHV)
Short Government
---
0.02
0
0.02
0.03
0.02
1,063,600
0.02


SPDR® S&P International Tech Sect ETF (IPK)
Technology

4.97
2.52
5.95
4.32
10.24
3,513
13.51


PowerShares Dynamic Networking ETF (PXQ)
Technology

3.81
3.31
7.06
8.96
8.92
4,258
16.99


PowerShares Dynamic Semiconductors ETF (PSI)
Technology

6.42
3.02
11.32
37.01
21.36
24,218
20.38


SPDR® S&P Semiconductor ETF (XSD)
Technology

10.57
3.35
14.73
32.96
20.32
101,189
24.78


DB Agriculture Short ETN (ADZ)
Trading-Inverse Commodities
---
15.52
0
15.52
19.64
11.47
2,271
15.52


DB Gold Short ETN (DGZ)
Trading-Inverse Commodities
---
1.28
5.91
4.75
15.94
9.95
90,241
22.48


DB Base Metals Short ETN (BOS)
Trading-Inverse Commodities
---
12.12
5.58
14.82
4.84
9.37
2,360
31.56


VelocityShares 3x Inv Natural Gas ETN (DGAZ)
Trading-Inverse Commodities
---
-11.69
0
68.25
88.32
-52.17
10,852,250
68.25


United States Short Oil ETF (DNO)
Trading-Inverse Commodities
---
13.38
15.3
23.52
84.65
23.38
18,058
69.42


VelocityShares 3x Inverse Gold ETN (DGLD)
Trading-Inverse Commodities
---
4.54
19.32
14.23
46.91
20.38
20,557
72.19


DB Crude Oil Short ETN (SZO)
Trading-Inverse Commodities
---
14.17
18.2
22.4
85.32
23.49
3,299
77


ProShares UltraShort Silver (ZSL)
Trading-Inverse Commodities
---
-3.02
21.68
12.9
56.08
31.86
58,970
77.94


VelocityShares 3x Inverse Silver ETN (DSLV)
Trading-Inverse Commodities
---
-7.49
34.33
12.98
74.14
30.86
56,041
115.97


ProShares UltraShort Bloomberg Crude Oil (SCO)
Trading-Inverse Commodities
---
17.66
28.52
32.34
193.45
39.99
1,358,426
117.9


DB Crude Oil Double Short ETN (DTO)
Trading-Inverse Commodities
---
38.11
36.75
50.79
221.17
46.68
125,897
161.04


VelocityShares 3x Inverse Crude Oil ETN (DWTI)
Trading-Inverse Commodities
---
21.17
48.07
43.28
358.66
53.62
148,804
187.49


DB 3x Inverse Japanese Govt Bd Futs ETN (JGBD)
Trading-Inverse Debt
---
-1.3
-2.38
-2.19
-10.5
-8.32
17,006
-9.33


ProShares Short Investment Grade Corp (IGS)
Trading-Inverse Debt
---
-1.73
-1.8
-2.21
-8.39
-7.09
2,079
-7.61


iPath® US Treasury 2-year Bear ETN (DTUS)
Trading-Inverse Debt
---
-6.02
-0.92
-2.69
-11.18
-6.46
5,468
-5.45


Direxion Daily 20+ Yr Trsy Bear 3X ETF (TMV)
Trading-Inverse Debt
---
-6.93
1.23
-7.72
-49.3
-27.76
586,536
-4.03


ProShares UltraShort TIPs (TPS)
Trading-Inverse Debt
---
-0.92
1.86
0.51
-4.8
-1.43
5,789
6.09


Barclays Inverse US Treasury Aggt ETN (TAPR)
Trading-Inverse Debt
---
-6.69
4.02
-4.64
---
---
3,497
7.42


iPath® US Treasury 10-year Bear ETN (DTYS)
Trading-Inverse Debt
---
-9.36
2.78
-0.55
-24.06
-13.32
51,734
7.79


iPath® US Treasury 5-year Bear ETN (DFVS)
Trading-Inverse Debt
---
-4.9
3.47
0.69
-9.92
-6.67
5,045
11.1


iPath® US Treasury Long Bond Bear ETN (DLBS)
Trading-Inverse Debt
---
-4.57
3.79
1.91
-32.74
-15.04
12,428
13.28


iPath® Short Enh MSCI Emerg Mkts ETN (EMSA)
Trading-Inverse Equity
---
-19.69
-19.69
-19.69
-19.69
-12.11
248
-78.76


ProShares UltraShort Nasdaq Biotech (BIS)
Trading-Inverse Equity
---
-24.89
-12.91
-25.07
-55.86
-58.41
430,724
-63.8


ProShares UltraShort MSCI Japan (EWV)
Trading-Inverse Equity
---
-19.55
-8.47
-19.36
-27.95
-24.79
7,198
-44.77


DB Agriculture Double Long ETN (DAG)
Trading-Leveraged Commodities
---
-19.62
-10.88
-23.58
-49.76
-27.39
19,957
-56.22


DB Gold Double Long ETN (DGP)
Trading-Leveraged Commodities
---
-6.13
-12.18
-11.94
-31.79
-25.54
126,454
-48.48


ProShares Ultra Gold (UGL)
Trading-Leveraged Commodities
---
-5.29
-11.76
-11.53
-31.5
-25.74
51,929
-46.81


DB Commodity Double Long ETN (DYY)
Trading-Leveraged Commodities
---
-2.84
-4.85
-12.56
-51.38
-29.32
4,340
-27.11


DB Base Metals Double Long ETN (BDD)
Trading-Leveraged Commodities
---
-12.9
-2.39
-16.77
-15.27
-20.95
4,000
-23.94


iPath® US Treasury Long Bond Bull ETN (DLBL)
Trading-Leveraged Debt
---
-0.99
-6.18
2.25
12.4
4.57
4,494
-16.29


Guggenheim S&P 500® Eq Wt Utilities ETF (RYU)
Utilities

-6.48
-4.4
-2.8
13.47
13.52
48,922
-16


Utilities Select Sector SPDR® ETF (XLU)
Utilities

-7.92
-3.74
-4.45
10.59
10.93
13,281,492
-15.67


PowerShares DWA Utilities Momentum ETF (PUI)
Utilities

-7.09
-4.09
-2.91
5.37
12.03
8,669
-15.18


iShares US Utilities (IDU)
Utilities

-7.64
-3.59
-4.27
10.17
11.15
346,531
-15.04


First Trust Utilities AlphaDEX® ETF (FXU)
Utilities

-5.69
-4.54
-1.37
12.27
13.05
560,666
-14.99


Vanguard Utilities ETF (VPU)
Utilities

-7.57
-3.54
-4.06
9.88
11.45
182,460
-14.68


Fidelity® MSCI Utilities ETF (FUTY)
Utilities

-7.62
-3.48
-4.01
9.97
---
130,501
-14.45


PowerShares S&P SmallCap Utilities ETF (PSCU)
Utilities

-6.27
-1.7
0.35
10.58
10.45
3,691
-4.75


ProShares Ultra VIX Short-Term Futures (UVXY)
Volatility
---
-27.51
-19.23
-39.39
-74.75
-87.65
11,873,874
-97.08


VelocityShares Daily 2x VIX ST ETN (TVIX)
Volatility
---
-25
-19.46
-37.65
-74.19
-88.53
21,338,667
-96.03


C-Tracks ETN linked to Citi Volatil TR (CVOL)
Volatility
---
-16.98
-5.53
-31.67
-71.58
-81.73
105,850
-48.26


VelocityShares VIX ST ETN (VIIX)
Volatility
---
-11.17
-9.3
-17.22
-39.21
-56.42
64,619
-45.12


ProShares VIX Short-Term Futures (VIXY)
Volatility
---
-11.05
-9.32
-17.06
-39.25
-56.45
1,196,955
-45.02


iPath® S&P 500 VIX ST Futures™ ETN (VXX)
Volatility
---
-10.82
-9.27
-17.08
-39.05
-56.44
40,338,209
-44.89


VelocityShares Daily 2x VIX MT ETN (TVIZ)
Volatility
---
-8.76
-8.13
-19.3
-42.18
-63.97
4,977
-43.69


VelocityShares VIX MT ETN (VIIZ)
Volatility
---
-3.67
-7.82
-8.58
-20.35
-37.96
2,852
-32.04


ProShares VIX Mid-Term Futures (VIXM)
Volatility
---
-3.55
-3.45
-8.98
-20.72
-37.45
60,444
-19.33


iPath® S&P 500 VIX MT Futures™ ETN (VXZ)
Volatility
---
-3.51
-3.36
-8.72
-20.08
-37.27
988,823
-18.8


iPath® S&P 500 Dynamic VIX ETN (XVZ)
Volatility
---
-0.6
0.04
-4.43
-11.42
-21.36
18,689
-4.31


UBS ETRACS S&P 500 VEQTOR Switch ETN (VQTS)
Volatility
---
-0.04
-0.66
-0.37
---
---
5,135
-2.35


iPath® Inverse S&P 500 VIX ST Fut™ ETN (XXV)
Volatility
---
-0.09
0.07
-0.14
-0.37
2.46
1,992
0.07


iPath® Inverse S&P 500 VIX ST Fut™ETN II (IVOP)
Volatility
---
-0.08
0.34
-0.45
0.24
8.98
13,987
0.57


UBS ETRACS Daily Long-Short VIX ETN (XVIX)
Volatility
---
1.37
1.06
-1.15
-4.12
-11.82
8,801
2.03


VelocityShares Daily Inverse VIX MT ETN (ZIV)
Volatility
---
0.34
2.77
5.3
11.64
42.91
86,241
13.61


ProShares Short VIX Short-Term Futures (SVXY)
Volatility
---
1.99
8.04
5.73
8.77
45.38
1,546,386
29.85


VelocityShares Daily Inverse VIX ST ETN (XIV)
Volatility
---
2.09
8.13
5.58
8.68
46.41
14,889,292
29.97


SPDR® SSgA Multi-Asset Real Return ETF (RLY)
World Allocation

-3.23
-5.91
-1.38
-8.84
---
51,508
-19.11


International MA Divers Inc ETF (YDIV)
World Allocation

-3.16
-3.57
-1.97
-3.53
---
5,788
-12.68


JPMorgan Diversified Return Glbl Eq ETF (JPGE)
World Stock

2.59
-0.56
4.61
---
---
6,307
2.93


SPDR® MSCI World Quality Mix ETF (QWLD)
World Stock

1.48
-0.32
4.15
---
---
1,777
3.19


EGShares Blue Chip ETF (BCHP)
World Stock

3.05
0.12
6.28
---
---
5,565
6.64


WBI SMID Tactical Select ETF (WBID)
World Stock

1.33
0.17
6.32
---
---
30,570
6.83


Guggenheim S&P High Income Infras ETF (GHII)
World Stock

---
-5.09
---
---
---
3,780
#VALUE!


RevenueShares Global Growth ETF (RGRO)
World Stock

---
-6.29
---
---
---
1,442
#VALUE!

jolivanes
03-18-2015, 09:29 PM
How to attach a file to a post?


To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'. To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.


On this page, below the message box, you will find a button labelled 'Manage Attachments'. Clicking this button will open a new window for uploading attachments. You can upload an attachment either from your computer or from another URL by using the appropriate box on this page. Alternatively you can click the Attachment Icon to open this page.


To upload a file from your computer, click the 'Browse' button and locate the file. To upload a file from another URL, enter the full URL for the file in the second box on this page. Once you have completed one of the boxes, click 'Upload'.


Once the upload is completed the file name will appear below the input boxes in this window. You can then close the window to return to the new post screen.


What files types can I use? How large can attachments be?


In the attachment window you will find a list of the allowed file types and their maximum sizes. Files that are larger than these sizes will be rejected. There may also be an overall quota limit to the number of attachments you can post to the board.


How do I add an image to a post?


If you have uploaded an image as an attachment, you can click the arrow next to the 'Attachment Icon' and select it from the list. This will be inserted into your post and can be located where you want it displayed.


To include an image that is not uploaded as an attachment and is located on another website, you can do so by copying the full URL to the image, (not the page on which the image is located), and either pressing the 'Insert Image' icon or by typing before the URL and after it, ensuring that you do not have any spaces before or after the URL of the image. You can insert photos from your albums (?) in this way too.

snb
03-19-2015, 01:39 AM
You can simply use Excel's builtin 'autofilter'.

Paul_Hossler
03-19-2015, 07:18 AM
This is my guess at the simplest and most maintainable




Option Explicit

Sub DeleteSomeStuff()
Dim rRow As Range, rData As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'col a is blank, row 3 is blank
Set rData = ActiveSheet.Range("B4").CurrentRegion

For Each rRow In rData.Rows
With rRow

Application.StatusBar = "Checking row #" & .Row

'second col in rDate is col C on sheet
If UCase(.Cells(1, 2).Value) = "JAPAN STK" Then
.Cells(1, 2).Value = True
ElseIf UCase(.Cells(1, 2).Value) = "VOLATILITY" Then
.Cells(1, 2).Value = True
ElseIf UCase(Left(.Cells(1, 2).Value, 8)) = "TRADING-" Then
.Cells(1, 2).Value = True
ElseIf IsError(.Cells(1, 10).Value) Then
.Cells(1, 2).Value = True
End If
End With
Next


On Error Resume Next
ActiveSheet.Columns(3).SpecialCells(xlCellTypeConstants, xlLogical).EntireRow.Delete
On Error GoTo 0

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.StatusBar = False

End Sub

mal
03-19-2015, 07:34 AM
Here is a spread sheet with 3/4 of it removed.13037

mal
03-19-2015, 07:43 AM
Thank you I will continue to work on the problem.
Mal

jolivanes
03-19-2015, 09:59 AM
This should work. I was not able to include the "Trading-*" in the Array and make it work. (Excel 2013)



Sub Mal()
Dim lr As Long, arrFind
lr = Cells(Rows.Count, 2).End(xlUp).Row
arrFind = Array("Japan Stk", "Volatility")
Application.ScreenUpdating = False
With Sheets("Sheet1")
.AutoFilterMode = False
With .Range("A1:K" & lr)
.AutoFilter Field:=2, Criteria1:=arrFind, Operator:=xlFilterValues
.Range("A3:K" & lr).SpecialCells(12).EntireRow.Delete
.AutoFilter
.AutoFilter Field:=2, Criteria1:="Trading-*" 'arrFind
.Range("A3:K" & lr).SpecialCells(12).EntireRow.Delete
.AutoFilter
.AutoFilter Field:=10, Criteria1:="#VALUE!", Operator:=xlFilterValues
.Range("A3:K" & lr).SpecialCells(12).EntireRow.Delete
.AutoFilter
End With
End With
Application.ScreenUpdating = True
End Sub

snb
03-19-2015, 01:36 PM
@Joli


Sub M_snb()
With Sheets("Sheet1").cells(1).currentregion.resize(,11)
.AutoFilter 2, Array("Japan Stk", "Volatility"), 7
.offset(1).EntireRow.Delete
.AutoFilter 2, "Trading-*"
.offset(1).EntireRow.Delete
.AutoFilter
.columns(2).specialcells(-4123,16).entirerow.delete
End With
End Sub

mal
03-20-2015, 10:00 AM
Thank you again. This macro ran great. However, when I copied in a new set of raw data it would not run. My reason fro trying to automate this process is that I will get new data quite often and need to minimize my effort. Attached is a fresh full file with your macro in it.

mal
03-20-2015, 10:18 AM
Thanks for your work. I tried loading it into my spread sheet and when I ran it I got a white cross in a red circle with the number 400. Attached is my file with your code in it.

jolivanes
03-20-2015, 11:06 PM
Delete the top two empty rows of the attached workbooks and try again.
Let us know what happens because I did not try it.

jolivanes
03-20-2015, 11:25 PM
@snb
Thanks but what I was referring to was to have all three (Japan, Volatility and Trading) in one Array.
As a matter of fact, I played around with a similar code you have provided here, and that you had provided in another post previously, but could not not get it to work with all three in one array.
However, I do appreciate you providing us with your way of solving it. It keeps me on my toes and thinking.
But for ease of understanding I do sometimes supply a "longwinded" way of solving things.
I am not the brightest light bulb around in these forums (fora if you wish) and I tend to supply people with, in my mind, code that they can change to suit their needs themselves.
But I have to honest here, the little bit, and it is very much a little bit only, I know I have learned from the people on the forums.
Harstikke bedankt weer.
Thanks and Regards

snb
03-21-2015, 01:05 AM
@Joli

Starting with you endeavour I tried to do the same: all 3 criteria in 1 array.
It turns out to be impossible. I assume the rule is: an array to filter values can't contain wildcards ( I didn't know but thanks to you I/yuo.we stumbled upon it.
BTW my suggesstions are suggestions only: no intention to critisize anyone else. The more options the more choice.
Gegroet,

jolivanes
03-21-2015, 07:29 AM
@mal.
If you delete the three empty rows, Row1, Row2 and Row5, first it works like a well oiled machine. (TestSource Workbook)
or you could add the following line as the first line of your code (only if the same rows are empty all the time)

Range("1:1, 2:2, 5:5").Delete