VBA code only working up to 9999 rows
 

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

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

VBA code only working up to 9999 rows
 

modify this to suit.

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

--
Don Guillett
SalesAid Software

"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/

VBA code only working up to 9999 rows
 

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)

--
Regards,
Tom Ogilvy


"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/

VBA code only working up to 9999 rows
 

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/

VBA code only working up to 9999 rows
 

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

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