Hi there Excel gurus heres my little question
I am trying to write a program that looks in ColA (starting at A2, A1 is a header) and deletes all rows that do not meet around 30 different texts.
I am trying to filter out all the stocks that are not on the DJIA (30 stocks)
I'm not that familiar with VB but I can reading up and can understand simple code, if anyone has done something similar and has code that would be great. You could leave the strings as A,B,C,D... instead of the 30 stocks tickers
The data is huge, I have 40 worksheets containing T columns and 27xxx rows. I'm trying to reduce the size of the file from 110mb to something more manageable
Comments
OK, from what I can gather, you just need to check column A to see if it's in the list of 30 companies yes? We'll go with your example and assume the companies are named A,B,C etc. The following code should work:
[code]
Const DJIAlist As String = "|A|B|C|D|E|F|G|H|I|J|K|L|M|N|O|P|Q|R|S|T|U|V" & _
"|W|X|Y|Z|AA|AB|AC|AD|" 'list of companies, with a pipe "|" character before and after their name
Sub RunRemoval()
Dim IsComplete As Boolean
IsComplete = RemoveNonDJIA(ActiveWorkbook.ActiveSheet) 'Run removal function on current sheet
If IsComplete = True Then 'if it ran OK...
MsgBox "Everything looks kosher!"
Else
MsgBox "It's all gone Pete Tong!"
End If
End Sub
Public Function RemoveNonDJIA(xSht As Worksheet) As Boolean
Dim iRow As Integer
On Error GoTo WeGottaGetOuttaThisPlace
iRow = 2
Do
If InStr(1, DJIAlist, "|" & xSht.Cells(iRow, 1).Value & "|") > 0 Then 'if data in column A is in DJIA list...
iRow = iRow + 1 'go to next row
Else
xSht.Rows(iRow).EntireRow.Delete 'delete entire row
End If
Loop Until xSht.Cells(iRow, 1).Value = "" 'loop until blank cell found in column A
RemoveNonDJIA = True
Exit Function
WeGottaGetOuttaThisPlace:
RemoveNonDJIA = False
End Function
[/code]
This will delete any lines on the current sheet that aren't in the list of DJIA. Beware, this code stops when it reaches a blank cell in column A, so if you have any rows that are blank in col A, it'll stop too soon, unless you change the code to only stop when columns A and C are blank, or whatever combo works for your data. If you wish to run it on all sheets at once, use the following subroutine instead of "RunRemoval":
[code]
Sub RunAllRemovals()
Dim IsComplete As Boolean, IsGood As Boolean, xSht As Worksheet
IsGood = True
For Each xSht In ActiveWorkbook.Sheets
IsComplete = RemoveNonDJIA(xSht) 'Run removal function on each sheet
If IsComplete = False Then IsGood = False
Next
If IsGood = True Then 'if all ran OK...
MsgBox "Everything looks kosher!"
Else
MsgBox "Something's gone Pete Tong!"
End If
End Sub
[/code]
Hope this helps,
Dai
------------------------------------------
Do or do not, there is no try. |
------------------------------------------