Change

Set rng = Sh1.Range("D2", "D2" & lLastRow)
' to
Set rng = Sh1.Range("D2", "D" & lLastRow)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"sup191 " wrote in
message ...
I have some VBA code which is activated when anything is input
or pasted
into the D column of a spreadsheet. It takes out any spaces or
dashes
that are in the cells. My problem is that it only works for
cells
1-9999. If anything is input in cell 10,000+, the macro isn't
run.
Here's my code:

Sub AccountID()
Dim lLastRow As Long
Dim Sh1 As Worksheet, rng As Range

Set Sh1 = ThisWorkbook.Worksheets("Form")
lLastRow = Sh1.Cells(Rows.Count, "D").End(xlUp).Row
Set rng = Sh1.Range("D2", "D2" & lLastRow)

rng.Replace _
What:=" ", Replacement:="", _
searchorder:=xlByRows, MatchCase:=True

rng.Replace _
What:="-", Replacement:="", _
searchorder:=xlByRows, MatchCase:=True

rng.NumberFormat = "General"

Set Sh1 = Nothing
Set rng = Nothing
End Sub



What would be limiting this to 9999 lines?

TIA!
sup191


---
Message posted from http://www.ExcelForum.com/