Spell Checking Nonsense Words and deleting.

[jackiegreen]

I am making a very neat learning program for my beginning reading students. In order to make it, I have created a list of "start" sounds in column a, and a list of "ending" sounds in row 1. So for example, column a has the whole alphabet plus digraphs (sounds like "wh" and "th") etc. Column b has sounds like "at" or "ack" or "ox". Then I used this row and column to create a table with 5000+ "words" (or really sound combos, only a fraction are words! 46x117 possibilities) combining every "start" with every "ending" (So "at" will have "aat" and "bat" and "cat" and "dat" etc. but of those only "bat" and "cat" etc are real words.)

Now that I have this massive table of both nonsense and real words, I need to delete the nonsense or non-words from the 5000. I have been doing it manually for hours. Ugggg. Then I tried using the spell checker. The spell checker works, but it will also take me forever because for every nonsense word I have to (1) delete the text in the spelling window, (2) click "delete" on the spell check box (the delete appears after I have deleted the text in the spelling box.) So, it is two mouse clicks for every nonsense word (I think my wrist will explode after 9500 mouse clicks to get this done.) I know that if the spell check rejects the word, I just want to delete it automatically. So, is there a way to just have the spell checker not stop and ask my opinion every time and just delete everything that it can't find in the dictionary in one fell swoop?

Thanks, and I am really a novice on excel, so if the answer is really complicated I'll be stuck weeding out these nonsense words by hand (for weeks? for months? ugg!)

 

[Andrew Poulsom]

This will delete the non-words from column C starting in row 1:

Sub Test()
    Dim Rng As Range
    Dim x As Long
    Set Rng = Range("C1:C" & Range("C65536").End(xlUp).Row)
    For x = Rng.Rows.Count To 1 Step -1
        If Application.CheckSpelling(Rng.Cells(x, 1).Value) = False Then
            Rng.Cells(x, 1).Delete Shift:=xlUp
        End If
    Next x
End Sub

Adapt the range to suit.