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.Cou nt,
"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

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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"leerem" wrote in message
...
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.Cou nt,
"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

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
---------------
Jacob Skaria


"leerem" wrote:

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.Cou nt,
"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

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

many thanks once agian

"Don Guillett" wrote:

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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"leerem" wrote in message
...
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.Cou nt,
"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

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" wrote:

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
---------------
Jacob Skaria


"leerem" wrote:

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.Cou nt,
"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

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

If this post helps click Yes
---------------
Jacob Skaria


"leerem" wrote:

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" wrote:

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
---------------
Jacob Skaria


"leerem" wrote:

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.Cou nt,
"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