Spell Checking a Specific Range And Deleting Rows With Mis-Spelled Words in VBA

[OldRookie]

From VBA code, I want to spell check words in Range(“D15:Dxx”) where “xx” changes each time I run the macro. When it finds a misspelled word I want it to delete that entire row. I don’t want to spell check the entire Sheet because it takes too long. I’ve never done this before and everything I’ve read and codes I’ve looked at don’t seem to help. My VBA skills are primitive (self-taught) and somewhat rusty (18 years old) so I could really use some help. Would someone please write that code with plenty of comments explaining what’s going on. I would greatly appreciate it.
Thank you very much,
OldRookie

 

[JoeMo]

You can try this on a copy of your worksheet. I noticed, in a cursory test, that if a cell contains a string that begins with the 'word' Th where the first letter is a capital T, Excel's spell checker does not flag it as mis-spelled. Not sure why that is, but there may be other such idiosyncrasies.

If this provides what you want, I will try to answer your queries as to what its doing for lines you specify. I prefer that, rather than annotating it with comments at this stage.

Sub SpellCheckAndDelete()
Dim R As Range, c As Range, x As Boolean, Del As Range
Application.ScreenUpdating = False
Set R = Range("D15:D" & Cells(Rows.Count, "D").End(xlUp).Row)
For Each c In R
    If c.Value <> "" Then
        x = Application.CheckSpelling(c.Text)
        If Not x Then c.Value = "#N/A"
    End If
Next c
On Error Resume Next
Set Del = R.SpecialCells(xlCellTypeConstants, xlErrors)
On Error GoTo 0
If Not Del Is Nothing Then
    Del.EntireRow.Delete
Else
    MsgBox "no cells in range " & R.Address(0, 0) & " have mis-spelled words."
End If
Application.ScreenUpdating = True
End Sub

 

[OldRookie]

JoeMo,
Thank you for your reply. I really appreciate it.

Examining your code, I don’t fully understand it. However it appears there are provisions for counting how many rows will appear each time the subroutine runs. In addition, it appears to check for blank cells. Seeing this I realized I made an error. I should have mentioned, each time I run the subroutine, I do know exactly how many rows there will be but it does vary each time though. That is to say “xx” is known and varies each time I run the subroutine. Further, I also know there are no blank cells buried in Range(“D15:Dxx”); I check for that earlier. My apologies for not being explicit on these points. Perhaps the inclusion of these two features give rise to the idiosyncrasies you mentioned? May I impose upon you a little further, and ask you to write another code that spell checks and deletes rows with misspelled words in the Range(“D15”:”Dxx”) this time knowing how many rows there are and there are no blank cells. I’ll be away from my computer today so please leave the code you’ve already written. I would like to study the two side-by-side to improve my skills.

On the skills note, the only book I could find back in 2000 when I began learning VBA was John Walkenback’s “Excel 97 Programming For Windows For Dummies”. While it was a great starting place to transform my 1970’s FORTRAN background, today I need something more advanced; something to help me understand the coding you’ve used. Could you recommend a book to help me with that education?

Thank you again,
OldRookie

 

[JoeMo]

The code I provided applies to the range D15:Dyy where yy is the row of the last filled cell in col D. Is your xx the same as yy?

No need to remove the check for blank cells even if there are none.

The idiosyncrasies are unrelated to the code or your data, the one I noted appears when using Excel's built-in spell checker.

Have you tried the code I provided on your data?

 

[OldRookie]

JoMoe,
The code seems to work! Thank you very much! There are some circumstances where there are "words" left behind after row deletion that I don't recognize; I don't know if they are slang. The bigger issue is, in spell checking options, I selected repeated words. I wanted repeats to be deleted; I only need to see the word once. Is there a modifier to the spell checking command I can put into the coding you gave me that will do that? Or is that something I add after your coding?

Thank you again,
OldRookie

 

[JoeMo]

JoMoe,
The code seems to work! Thank you very much! There are some circumstances where there are "words" left behind after row deletion that I don't recognize; I don't know if they are slang. The bigger issue is, in spell checking options, I selected repeated words. I wanted repeats to be deleted; I only need to see the word once. Is there a modifier to the spell checking command I can put into the coding you gave me that will do that? Or is that something I add after your coding?

Thank you again,
OldRookie

You are welcome.
If you check the spell checker option (File> Options> Proofing) to flag repeated words, the code I posted will work if the repetition is within two consecutive words. That's the way the native Excel spell checker seems to work too. For example, for the string: "The fox jumped over the fox moon", the repeated word "fox" will not be flagged, while for "The fox fox jumped over the moon" it will be, and the code I posted will delete the row with the latter string in it.

If you post some sample data so we can see what you mean by "repeated words", there might be some additional code that would deal with the instances you want addressed.

 

[OldRookie]

JoMoe,
It's good to know the limitations of the spell checker for duplicate words. Duplicate words in my column D could be 100 rows apart. I handled the problem by dropping this in behind your coding:
ActiveSheet.Range("D14:D" & Cells(Rows.Count, "D").End(xlUp).Row).RemoveDuplicates Columns:=1, Header:=xlYes
I got the basic command by using the macro recorder. I selected a range with duplicates, went to the Data tab on the worksheet, selected "Remove Duplicates", then used the row count coding from your work. I had to start the range in row 14 because it wasn't considering duplicates of row 15 correctly. It works perfectly now though!

I would like to find a good VBA Excel book that would help me understand coding like what you wrote; one with examples to illustrate their points. My coding is what I would call "brute force" and as such, it's slow. I don't know if you can direct me to a good book without raising Mr. Excel endorsement concerns. If you can't, I understand.
Thank you for all of your assistance! Everything came out great!

OldRookie

 

[JoeMo]

You are welcome - thanks for your reply.

It's been years since I looked at any books on VBA for Excel. For more recent books, try googling "VBA for Excel books".