T
thomas donino
I have the following code module which is searching through a very large file
to find only the data we want, which are the case statements. The problem is
it does bold the data if i only use 1 case statement and it doesn't delete
the other rows. It is also a continuous loop for some reason.
Sub FormatCFTCFile()
' 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 = 2 To Finalrow
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"
Case "SOYBEAN MEAL - CHICAGO BOARD OF TRADE", "2-YEAR U.S. TREASURY
NOTES - CHICAGO BOARD OF TRADE", "SUGAR NO. 11 - ICE FUTURES U.S."
Case "5-YEAR U.S. TREASURY NOTES - CHICAGO BOARD OF TRADE", "10-YEAR
U.S. TREASURY NOTES - CHICAGO BOARD OF TRADE"
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"
Case "COFFEE C - ICE FUTURES U.S.", "SILVER - COMMODITY EXCHANGE INC.",
"COPPER-GRADE #1 - COMMODITY EXCHANGE INC.", "GOLD - COMMODITY EXCHANGE INC."
Case "JAPANESE YEN - CHICAGO MERCANTILE EXCHANGE", "EURO FX - CHICAGO
MERCANTILE EXCHANGE", "GASOLINE BLENDSTOCK (RBOB) - NEW YORK MERCANTILE
EXCHANGE"
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"
Case "NASDAQ-100 STOCK INDEX - CHICAGO MERCANTILE EXCHANGE",
"NASDAQ-100 STOCK INDEX (MINI) - CHICAGO MERCANTILE EXCHANGE"
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
'delete all rows of unnecessary data
Case Else
Cells(x, 1).EntireRow.Delete
End Select
Next x
' delete all unnecessary columns
End Sub
Thank you in advance
to find only the data we want, which are the case statements. The problem is
it does bold the data if i only use 1 case statement and it doesn't delete
the other rows. It is also a continuous loop for some reason.
Sub FormatCFTCFile()
' 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 = 2 To Finalrow
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"
Case "SOYBEAN MEAL - CHICAGO BOARD OF TRADE", "2-YEAR U.S. TREASURY
NOTES - CHICAGO BOARD OF TRADE", "SUGAR NO. 11 - ICE FUTURES U.S."
Case "5-YEAR U.S. TREASURY NOTES - CHICAGO BOARD OF TRADE", "10-YEAR
U.S. TREASURY NOTES - CHICAGO BOARD OF TRADE"
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"
Case "COFFEE C - ICE FUTURES U.S.", "SILVER - COMMODITY EXCHANGE INC.",
"COPPER-GRADE #1 - COMMODITY EXCHANGE INC.", "GOLD - COMMODITY EXCHANGE INC."
Case "JAPANESE YEN - CHICAGO MERCANTILE EXCHANGE", "EURO FX - CHICAGO
MERCANTILE EXCHANGE", "GASOLINE BLENDSTOCK (RBOB) - NEW YORK MERCANTILE
EXCHANGE"
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"
Case "NASDAQ-100 STOCK INDEX - CHICAGO MERCANTILE EXCHANGE",
"NASDAQ-100 STOCK INDEX (MINI) - CHICAGO MERCANTILE EXCHANGE"
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
'delete all rows of unnecessary data
Case Else
Cells(x, 1).EntireRow.Delete
End Select
Next x
' delete all unnecessary columns
End Sub
Thank you in advance