I know it worked when I select "AL1" and hold CTRL Key+Press Down Arrow Key and it jumps to "AL3" instead of going all the way down to "AL1001".
Does it make sense to you?
In your sample xlsm that takes me to the last row on the sheet
There don't seem to be any 0 length strings in your sample
Try this
Option Explicit
'clear any empty, but text i.e. 0 length strings
Sub RemoveEmptyStrings()
With Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("AL:At"))
Call .Replace(vbNullString, "###ZZZ###", LookAt:=xlWhole)
Call .Replace("###ZZZ###", vbNullString, LookAt:=xlWhole)
'clear the settings
.Find What:=vbNullString, After:=.Cells(1, 1), LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False
.Replace What:=vbNullString, Replacement:=vbNullString, ReplaceFormat:=False
End With
End Sub
Seems funny, but you do need both .Replace lines