ExcelBanter - Speed up routine

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Speed up routine (https://www.excelbanter.com/excel-programming/434779-speed-up-routine.html)

leerem

Speed up routine

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

Don Guillett

Speed up routine

Jacob Skaria

Speed up routine

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

leerem

Speed up routine

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

leerem

Speed up routine

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

Jacob Skaria

Speed up routine

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

All times are GMT +1. The time now is 10:23 AM.