Hi Thomas,

Just send a mail to you.

Wkr,

JP


"thomas donino" wrote in message
...
Here is the current code with all comments and suggestions implemented.
The
code does nothing now )-:

Sub FormatCFTCFile()

' figure out what row is the last row of data
Finalrow = Cells(Rows.Count, 1).End(xlUp).Row
Finalcol = Cells(1, Columns.Count).End(xlLeft).Column
' loop through all rows from row 2, where data starts, to the final row
but
starting in final row and working up
For x = Finalrow To 2 Step -1
Select Case Cells(x, 1)
'check for commodity names that we want to keep the data from
Case "WHEAT - CHICAGO BOARD OF TRADE", "CORN - CHICAGO BOARD OF
TRADE", "SOYBEANS - CHICAGO BOARD OF TRADE", "U.S. TREASURY BONDS -
CHICAGO
BOARD OF TRADE", _
"SOYBEAN MEAL - CHICAGO BOARD OF TRADE", "2-YEAR U.S. TREASURY
NOTES - CHICAGO BOARD OF TRADE", "SUGAR NO. 11 - ICE FUTURES U.S.", _
"5-YEAR U.S. TREASURY NOTES - CHICAGO BOARD OF TRADE", "10-YEAR
U.S. TREASURY NOTES - CHICAGO BOARD OF TRADE", _
"NO. 2 HEATING OIL, N.Y. HARBOR - NEW YORK MERCANTILE EXCHANGE",
"NATURAL GAS - NEW YORK MERCANTILE EXCHANGE", _
"CRUDE OIL, LIGHT SWEET - NEW YORK MERCANTILE EXCHANGE", "COFFEE
C
- ICE FUTURES U.S.", "SILVER - COMMODITY EXCHANGE INC.", _
"COPPER-GRADE #1 - COMMODITY EXCHANGE INC.", "GOLD - COMMODITY
EXCHANGE INC.", "JAPANESE YEN - CHICAGO MERCANTILE EXCHANGE", _
"EURO FX - CHICAGO MERCANTILE EXCHANGE", "GASOLINE BLENDSTOCK
(RBOB) - NEW YORK MERCANTILE EXCHANGE", _
"DOW JONES INDUSTRIAL AVG- x $5 - CHICAGO BOARD OF TRADE", "S&P
500 STOCK INDEX - CHICAGO MERCANTILE EXCHANGE", _
"DOW JONES INDUSTRIAL AVG- x $5 - CHICAGO BOARD OF TRADE",
"NASDAQ-100 STOCK INDEX - CHICAGO MERCANTILE EXCHANGE", _
"NASDAQ-100 STOCK INDEX (MINI) - CHICAGO MERCANTILE EXCHANGE",
"S&P GSCI COMMODITY INDEX - CHICAGO MERCANTILE EXCHANGE", "E-MINI S&P 400
STOCK INDEX - CHICAGO MERCANTILE EXCHANGE"
' if the row contains data we want, bold it
Cells(x, 1).EntireRow.Font.Bold = True
Case Else
Cells(x, 1).EntireRow.Delete
End Select
Next x
' delete all unnecessary columns
End Sub


Here is sample row in comma delimited
WHEAT - CHICAGO BOARD OF TRADE,090811,8/11/2009,001602,CBT, 00 001,324507




"thomas donino" wrote:

I made the changes which all make sense but the loop is still continuous
and
debugger said its breaking on the End Select at the bottom

"Joel" wrote:

When deleting rows you have to step backward from last row to first
row. If
you delete row 5 then what was row 6 becomes row 5 and you will skip
processing the orignal row 6 becasue the for loop went to the next row.
See
code below. I simply changed the For statement. I also made the code
more
readable.

You also have to add the bold for each case, otherwise, only one case
will
get higlighted


Sub FormatCFTCFile()

wheatBoards = Array("WHEAT - CHICAGO BOARD OF TRADE", _
"CORN - CHICAGO BOARD OF TRADE", _
"U.S. TREASURY BONDS - CHICAGO BOARD OF TRADE")

' figure out what row is the last row of data
Finalrow = Cells(Rows.Count, 1).End(xlUp).Row
' loop through all rows from row 2 where data starts to the final
row
For x = Finalrow To 2 Step -1
Select Case Cells(x, 1)
'check for commodity names that we want to keep the data from
Case "WHEAT - CHICAGO BOARD OF TRADE", _
"CORN - CHICAGO BOARD OF TRADE", _
"SOYBEANS - CHICAGO BOARD OF TRADE", _
"U.S. TREASURY BONDS - CHICAGO BOARD OF TRADE"

' if the row contains data we want, bold it
Cells(x, 1).EntireRow.Font.Bold = True

Case "SOYBEAN MEAL - CHICAGO BOARD OF TRADE", _
"2-YEAR U.S. TREASURY NOTES - CHICAGO BOARD OF TRADE", _
"SUGAR NO. 11 - ICE FUTURES U.S."

' if the row contains data we want, bold it
Cells(x, 1).EntireRow.Font.Bold = True

Case "5-YEAR U.S. TREASURY NOTES - CHICAGO BOARD OF TRADE", _
"10-YEAR U.S. TREASURY NOTES - CHICAGO BOARD OF TRADE"

' if the row contains data we want, bold it
Cells(x, 1).EntireRow.Font.Bold = True

Case "NO. 2 HEATING OIL, N.Y. HARBOR - NEW YORK MERCANTILE
EXCHANGE", _
"NATURAL GAS - NEW YORK MERCANTILE EXCHANGE", _
"CRUDE OIL, LIGHT SWEET - NEW YORK MERCANTILE EXCHANGE"

' if the row contains data we want, bold it
Cells(x, 1).EntireRow.Font.Bold = True

Case "COFFEE C - ICE FUTURES U.S.", _
"SILVER - COMMODITY EXCHANGE INC.", _
"COPPER-GRADE #1 - COMMODITY EXCHANGE INC.", _
"GOLD - COMMODITY EXCHANGE INC."

' if the row contains data we want, bold it
Cells(x, 1).EntireRow.Font.Bold = True

Case "JAPANESE YEN - CHICAGO MERCANTILE EXCHANGE", _
"EURO FX - CHICAGO MERCANTILE EXCHANGE", _
"GASOLINE BLENDSTOCK (RBOB) - NEW YORK MERCANTILE
EXCHANGE"

' if the row contains data we want, bold it
Cells(x, 1).EntireRow.Font.Bold = True

Case "DOW JONES INDUSTRIAL AVG- x $5 - CHICAGO BOARD OF TRADE",
_
"S&P 500 STOCK INDEX - CHICAGO MERCANTILE EXCHANGE", _
"DOW JONES INDUSTRIAL AVG- x $5 - CHICAGO BOARD OF TRADE"

' if the row contains data we want, bold it
Cells(x, 1).EntireRow.Font.Bold = True

Case "NASDAQ-100 STOCK INDEX - CHICAGO MERCANTILE EXCHANGE", _
"NASDAQ-100 STOCK INDEX (MINI) - CHICAGO MERCANTILE
EXCHANGE"

' if the row contains data we want, bold it
Cells(x, 1).EntireRow.Font.Bold = True

Case "S&P GSCI COMMODITY INDEX - CHICAGO MERCANTILE EXCHANGE",
_
"E-MINI S&P 400 STOCK INDEX - CHICAGO MERCANTILE EXCHANGE"

' if the row contains data we want, bold it
Cells(x, 1).EntireRow.Font.Bold = True

Case Else
Cells(x, 1).EntireRow.Delete
End Select
Next x
' delete all unnecessary columns
End Sub