Spell Check - Change All

Hi! I am trying to clean up a large spreadsheet (~500,000 rows) with a lot of spelling errors. The problem I'm having is that choosing "Change All" only applies the change to "all" if you complete the spell check. Due to the large number of errors it is not possible for me to fix all the errors in one sitting, therefore I have to close the spell check window prior to finishing which means the "Change All" is not applied to anything past where I stopped. I would love to figure out a way to have any "Change All" applied to the whole spreadsheet immediately so I don't have to address the same correction in multiple sessions.

Thanks in advance!

Example: If I run spell check on this list, the checker will stop on #4 and suggest "words", if I click "Change All" #4 will be corrected and the checker will move to #9 to suggest a correction. If I close out the checker at this point, #12 will still be misspelled despite earlier choosing "Change All" for the same error.



1 word
2 test
3 another word
4 wrds
5 again
6 cake
7 pizza
8 mistake
9 prblem
10 correct
11 right
12 wrds
13 spatula

.
This macro will edit the supplied term on all sheets. You can edit the macro to focus on only a single sheet :

Option Explicit

Sub FindReplaceAll_CountReplacements()
'PURPOSE: Find & Replace text/values throughout entire workbook, notify user of how many cells were affected
'SOURCE: www.TheSpreadsheetGuru.com

Dim sht As Worksheet
'Dim fnd As Variant
'Dim rplc As Variant
Dim ReplaceCount As Long

Dim fnd, rplc As String

fnd = InputBox("Enter OLD term. ", "Old Term ?")
rplc = InputBox("Enter NEW term. ", "New Term ?")

For Each sht In ActiveWorkbook.Worksheets

  ReplaceCount = ReplaceCount + Application.WorksheetFunction.CountIf(sht.Cells, "*" & fnd & "*")

  sht.Cells.Replace what:=fnd, Replacement:=rplc, _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False
    
Next sht

MsgBox "I have completed my search and made replacements in " & ReplaceCount & " cell(s)."

End Sub

Let me know if you need assistance with editing the macro.

Thanks for the quick reply -- if I understand the macro correctly, this is basically a "find and replace". I am wanting spell check to discover the errors and let me decide if I want to use the suggested correction . . . and if I choose "change all" I'd like for it to apply the change to all the same errors down the line and continue on to the next spelling error.

.
The following locates the first spelling error then corrects all identical errors in the sheet.

Option Explicit

Sub spellCheck()

Sheet1.Cells.CheckSpelling

End Sub

My apologies for not indicating the cross post.

This is what I experience using the macro on the short example in my original post:

• I start macro
• spell-check window opens for row 4 error ("wrds")
• I click "Change All" to change to the suggested correction ("words") and row 4 is corrected
• spell-check window goes to row 9 error ("prblem")
• if at this point I click "Close" or the "X" at the top of the screen because I do not have time to go through the whole spreadsheet the error on row 12 ("wrds") is still there even though I did a "Change All" on the same error (imagine that the sheet has 400,000 rows with 5,000 spelling errors and I've gotten to row 100,000 and don't have time to spell-check the rest of the sheet right now)
• however, if I were to correct the error on line 9, then the spell-check would automatically correct row 12 and finish the spell-check

Thanks again for all your help!