Perplexing Macro Issue

Hi

A couple of thoughts.

1) You don't need to select ranges to action them.
Your findreplace code can then be changed to

With Columns("A:A")
    .Replace What:="Apple", Replacement:="Red Delicious Apple”, LookAt:=    xlPart , SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False,ReplaceFormat:=False"
    .Replace What:="Orange", Replacement:="Tangerine”, LookAt        :=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False"
    .Replace What:="Red Grpaes", Replacement:="Red Grapes”, LookAt:=        xlPart , SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False"
  End With

Unfortunately when used this way, the _ separation won't work.

2) In a similar vein, the separateState can be changed to

Columns("I:I").Insert Shift:=xlToRight
  For i = 2 To Cells(Rows.Count, 10).End(xlUp).Row
    Cells(i, "I").Value = Right(Cells(i, "J").Value, 2)
  Next i
  
  Range("I1").Value = "State"
    With Range("I1").Characters(Start:=1, Length:=5).Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 9
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
    End With
    Columns("I:I").Columns("I:I").EntireColumn.AutoFit

It only operates on the filled range and doesn't put formulas in that have to be recalculated, so this should cut down on some processing time.

3) With the splitcells macro, do you really need the output in columns E:H, or could they be in A:D? No real drama, but this can be done without formulas. You also have a lot of formulas being created (for every row) and this could be cut down to only action cells in column A that have data. As I read it you have space separated text in column A. If A:D can be used for output then something like

Columns("B:D").Insert shift:=xlToRight
  Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row).TextToColumns DataType:=xlDelimited, Space:=True
  Range("A1:D1").Value = Array("Year", "Make", "Model", "Model-other")

Will look further as time permits.

rylo