Thanks to the answer from Noodles I know that my UDF is the main cause of lag in my excel workbook. Can this UDF be re-written to not cause the lag? I am using this UDF with an excel formula (example =IF(OR(ISNUMBER($DH2),$DH2>" "),LookUpConcat($B2,Usage!$AM$2:$AM$5000,Usage!$AS$2:$AS$5000," ")," ")). Maybe the formula needs changed. The formula is pulled down through many cells and is adjusted to work in 2 different columns in the workbook. I am not IT so Noodles answer to my last lag question was not totally understood by me, but gave me enough information to know that this is the macro causing the most lag issue.
Function LookUpConcat(ByVal SearchString As String, SearchRange As Range, ReturnRange As Range, _
Optional Delimiter As String = " ", Optional MatchWhole As Boolean = True, _
Optional UniqueOnly As Boolean = False, Optional MatchCase As Boolean = False)
Dim X As Long, CellVal As String, ReturnVal As String, Result As String
If (SearchRange.Rows.Count > 1 And SearchRange.Columns.Count > 1) Or _
(ReturnRange.Rows.Count > 1 And ReturnRange.Columns.Count > 1) Then
LookUpConcat = CVErr(xlErrRef)
Else
If Not MatchCase Then SearchString = UCase(SearchString)
For X = 1 To SearchRange.Count
If MatchCase Then
CellVal = SearchRange(X).Value
Else
CellVal = UCase(SearchRange(X).Value)
End If
ReturnVal = ReturnRange(X).Value
If MatchWhole And CellVal = SearchString Then
If UniqueOnly And InStr(Result & Delimiter, Delimiter & ReturnVal & Delimiter) > 0 Then GoTo Continue
Result = Result & Delimiter & ReturnVal
ElseIf Not MatchWhole And CellVal Like "*" & SearchString & "*" Then
If UniqueOnly And InStr(Result & Delimiter, Delimiter & ReturnVal & Delimiter) > 0 Then GoTo Continue
Result = Result & Delimiter & ReturnVal
End If
Continue:
Next
LookUpConcat = Mid(Result, Len(Delimiter) + 1)
End If
End Function
The answer to this is, Thanks to Mark Fitzgerald, nothing is wrong with the UDF the lag is caused by the amount of search range. I adjusted the search range and that took care of the lag.