Deleting Rows based on not meeting several text criteria

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. |
    ------------------------------------------
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

In this Discussion