VBA code only working up to 9999 rows

S

sup191

I have some VBA code which is activated when anything is input or paste
into the D column of a spreadsheet. It takes out any spaces or dashe
that are in the cells. My problem is that it only works for cell
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!
sup19
 
D

Don Guillett

modify this to suit.

Sub replaceem()
For Each c In Range("g2:g6")
c.Value = Application.Substitute(c, "-", "")
Next
End Sub
 
T

Tom Ogilvy

Shouldn't

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

be

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

or

Set rng = Sh1.Range("D2:D" & lLastRow)
 
C

Chip Pearson

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
 
S

sup191

Thanks guys!! The "D2" to "D" fixed it. I needed to omit cell D1 as i
was the header to the column and I didn't want it to be effected by th
macro. Now I see why it should just be "D".

My eyes are opened a little wider... :)




sup19
 
Top