Speed up routine

[leerem]

Hi, I have a routine that takes an age to run;
Users input data of upto 200-300 lines of data per day eight columns per
line.
at the end of the week before finalising the end of week reports, I need to
check that the info has been entered correctly without any errors. I have the
following code which I need assistance with. How can i speed this up.

this is then mulitipled by 12 sites each site has to be checked

Sub SheetCheck()

Dim Y As Integer
Dim Z As Integer
Dim strInput As Variant
Dim Lastrow As Integer

Lastrow = ActiveWorkbook.Worksheets("sheet1").Cells(Rows.Count,
"B").End(xlUp).Row

With ActiveWorkbook.Sheets("Sheet1").Range("C2")
For Z = 0 To Lastrow ' can be anything from 900 to 2,200 Rows
For Y = 0 To 8 ' No. of columns

' total cells to examine upto 17,600

strInput = .Offset(Z, Y)
strInput = Replace(strInput, "_", "")
strInput = Replace(strInput, ",", "")
strInput = Replace(strInput, "+", "")
strInput = Replace(strInput, "-", "")
strInput = Replace(strInput, ":", "")
strInput = Replace(strInput, "=", "")
strInput = Replace(strInput, "/", "")
strInput = Replace(strInput, "*", "")
strInput = Replace(strInput, "?", "")
strInput = Replace(strInput, ".", "")
strInput = Replace(strInput, " ", "")

.Offset(Z, Y) = strInput
Next
Next
End With
End Sub

any help would be greatly appreciated

 

[Don Guillett]

sub ff()
with sheets("sheet1")
Lastrow = .Cells(Rows.Count,"B").End(xlUp).Row
with .range(cells(1,"c"),cells(lr,10))
.Replace "_", ""
.Replace ",", ""
'etc

end with
end with
end sub

 

[Jacob Skaria]

Try the below version

Sub Macro()
Dim ws As Worksheet, rngTemp As Range, intTemp As Integer
Set ws = ActiveWorkbook.Sheets("Sheet1")

Set rngTemp = ws.Range("C2:K" & ws.Cells(Rows.Count, _
"B").End(xlUp).Row)
strFind = "_,+-:=/. "
For intTemp = 1 To Len(strFind)
rngTemp.Replace Mid(strFind, intTemp, 1), ""
Next
rngTemp.Replace "~*", ""
rngTemp.Replace "~?", ""
End Sub

If this post helps click Yes

 

[leerem]

Great works a treat, I seem to go round the long way dont I ?

many thanks once agian

 

[leerem]

just a quick question, when useing the procedure above the "*" and the "?"
keys seem to blank all the data, do these act as wild cards and therefore
delete the contents of the sheet?

 

[Jacob Skaria]

Yes; and that is why you notice those are handled separately in the macro
which I posted...

If this post helps click Yes