I have a Macro that was written by someone else. This is a Macro/VB module we use to scrub data so that it will cleanup a number of special characters and style tags. I have detected a bug that it will only run against a spreadsheet until it encounters the last blank line in column "B". It needs to run as long as there is a value present in column "A".
What happens is we have rows of data and for whatever reason a value is not needed any longer in Column B and yet there might be 10-100 more rows and those still need to be scrubbed for all the other columns of data. My cheating work around is to copy one of the top rows to the very last row in the file. I just wish I did not have to continue to do this.
Below is the macro and sample of a spreadsheet
Can someone help me get this to run to the last line of column A every time?
thanks
Joanie
Sub cleanAttributes()
'********************************************************************************
'* This macro will replace special characters with the Agility special
'* character tag.
'* It will also trim off any extra white-space characters at the beginning or
'* end of the text.
'* As written, this macro is expecting the text to be modified to be in Column B
'********************************************************************************
Dim lrow, lcol As Long
Dim x, r, c As Long
Dim xCode As Long
Dim origString As String
lrow = Cells(Rows.count, 2).End(xlUp).row
lcol = Cells(1, Columns.count).End(xlToLeft).Column
For x = 2 To lcol
Columns(x).NumberFormat = "@"
Next x
For r = 2 To lrow
For c = 2 To lcol
If Cells(r, c).value <> "" Then
origString = Cells(r, c).Text
origString = Trim(origString)
' Anything less than 32, remove
For I = 1 To 9
origString = Replace$(origString, ChrW$(I), "")
Next I
origString = Replace$(origString, ChrW$(13) & ChrW$(10), "<special id="" 70""="">") '
origString = Replace$(origString, ChrW$(13), "<special id="" 70""="">") '
origString = Replace$(origString, ChrW$(10), "<special id="" 70""="">") '
origString = Replace$(origString, ChrW$(11), "")
origString = Replace$(origString, ChrW$(12), "")
For I = 14 To 31
origString = Replace$(origString, ChrW$(I), "")
Next I
origString = Replace$(origString, ChrW$(37), "<special id="" 15""="">") '%
origString = Replace$(origString, ChrW$(38), "<special id="" 64""="">") '&
origString = Replace$(origString, ChrW$(64), "<special id="" 65""="">") '@
origString = Replace$(origString, ChrW$(126), "<special id="" 22""="">") '~
' 128 to 159, remove
For I = 128 To 159
origString = Replace$(origString, ChrW$(I), "")
Next I
origString = Replace$(origString, ChrW$(160), " ")
origString = Replace$(origString, ChrW$(161), "i")
origString = Replace$(origString, ChrW$(162), "<special id="" 68""="">") 'CENT
origString = Replace$(origString, ChrW$(163), "<special id="" 78""="">") 'Pound (money)
origString = Replace$(origString, ChrW$(164), "<special id="" 81""="">") 'Box
origString = Replace$(origString, ChrW$(165), "<special id="" 27""="">") 'Yen
origString = Replace$(origString, ChrW$(167), "<special id="" 19""="">") 'Section
origString = Replace$(origString, ChrW$(169), "<special id="" 69""="">") 'Copyright
origString = Replace$(origString, ChrW$(172), "")
origString = Replace$(origString, ChrW$(174), "<special id="" 18""="">") 'Registered Mark
origString = Replace$(origString, ChrW$(176), "<special id="" 74""="">") 'Degree
origString = Replace$(origString, ChrW$(177), "<special id="" 17""="">") 'Plus/Minus
origString = Replace$(origString, ChrW$(178), "<style id="" 8""="">2</style>") 'Square
origString = Replace$(origString, ChrW$(179), "<style id="" 8""="">3</style>") 'Cube
origString = Replace$(origString, ChrW$(180), "'")
origString = Replace$(origString, ChrW$(181), "<special id="" 8""="">") 'Micro
origString = Replace$(origString, ChrW$(182), "<special id="" 14""="">") 'Paragraph
origString = Replace$(origString, ChrW$(183), "<special id="" 25""="">") 'Bullet
origString = Replace$(origString, ChrW$(185), "")
origString = Replace$(origString, ChrW$(186), "<special id="" 230""="">") 'Ellipsis
origString = Replace$(origString, ChrW$(188), "<fraction d="" 4""="" n="" 1""="">") '1/4
origString = Replace$(origString, ChrW$(189), "<fraction d="" 2""="" n="" 1""="">") '1/2
origString = Replace$(origString, ChrW$(190), "<fraction d="" 4""="" n="" 3""="">") '3/4
origString = Replace$(origString, ChrW$(213), "'")
origString = Replace$(origString, ChrW$(214), "<special id="" 239""="">") 'O with umlaut
origString = Replace$(origString, ChrW$(215), "<special id="" 23""="">") 'Times Symbol
origString = Replace$(origString, ChrW$(216), "<special id="" 1""="">") 'Phi, Capital
origString = Replace$(origString, ChrW$(220), "<special id="" 26""="">") 'U with Umlaut
origString = Replace$(origString, ChrW$(233), "<special id="" 76""="">") 'E-accent
origString = Replace$(origString, ChrW$(247), "<special id="" 75""="">") 'Division Sign
origString = Replace$(origString, ChrW$(248), "<special id="" 2""="">") 'Phi, lower case
origString = Replace$(origString, ChrW$(934), "<special id="" 2""="">") 'Phi, lower case
origString = Replace$(origString, ChrW$(402), "<special id="" 219""="">") 'F latin
origString = Replace$(origString, ChrW$(650), "<special id="" 11""="">") 'Omega
origString = Replace$(origString, ChrW$(730), "<special id="" 74""="">") 'Degree
origString = Replace$(origString, ChrW$(778), "0<special id="" 74""="">") 'Degree
origString = Replace$(origString, ChrW$(913), "<special id="" 86""="">") 'Alpha
origString = Replace$(origString, ChrW$(915), "<special id="" 91""="">") 'Gamma
origString = Replace$(origString, ChrW$(916), "<special id="" 83""="">") 'Delta
origString = Replace$(origString, ChrW$(920), "<special id="" 93""="">") 'Theta
origString = Replace$(origString, ChrW$(923), "<special id="" 92""="">") 'Lambda
origString = Replace$(origString, ChrW$(931), "<special id="" 240""="">") 'Sigma
origString = Replace$(origString, ChrW$(937), "<special id="" 11""="">") 'Omega
origString = Replace$(origString, ChrW$(945), "<special id="" 225""="">") ' Alpha LC
origString = Replace$(origString, ChrW$(946), "<special id="" 20""="">") 'Beta
origString = Replace$(origString, ChrW$(947), "<special id="" 91""="">") 'Gamma
origString = Replace$(origString, ChrW$(949), "<special id="" 226""="">") 'Epsilon LC
origString = Replace$(origString, ChrW$(951), "<special id="" 227""="">") 'Eta LC
origString = Replace$(origString, ChrW$(952), "<special id="" 93""="">") 'Theta
origString = Replace$(origString, ChrW$(955), "<special id="" 92""="">") 'Lambda
origString = Replace$(origString, ChrW$(956), "<special id="" 8""="">") 'Micro
origString = Replace$(origString, ChrW$(960), "<special id="" 16""="">") 'Pi
origString = Replace$(origString, ChrW$(964), "<special id="" 224""="">") 'Tau LC
origString = Replace$(origString, ChrW$(8208), "<special id="" 9""="">") 'Minus
origString = Replace$(origString, ChrW$(8211), "<special id="" 9""="">") 'Minus
origString = Replace$(origString, ChrW$(8212), "<special id="" 7""="">") 'Long Dash
origString = Replace$(origString, ChrW$(8216), "<special id="" 13""="">") 'Quote Open Single
origString = Replace$(origString, ChrW$(8217), "<special id="" 71""="">") 'Quote Close Single
origString = Replace$(origString, ChrW$(8217), "<special id="" 82""="">") 'Apostrophe
origString = Replace$(origString, ChrW$(8220), "<special id="" 12""="">") 'Quotes Open Double
origString = Replace$(origString, ChrW$(8221), "<special id="" 4""="">") 'Inch Mark
origString = Replace$(origString, ChrW$(8221), "<special id="" 67""="">") 'Quotes Close Double
origString = Replace$(origString, ChrW$(8224), "<special id="" 72""="">") 'Dagger
origString = Replace$(origString, ChrW$(8225), "<special id="" 73""="">") 'Double Dagger
origString = Replace$(origString, ChrW$(8226), "<special id="" 25""="">") 'Bullet
origString = Replace$(origString, ChrW$(8230), "<special id="" 230""="">") 'Ellipsis
origString = Replace$(origString, ChrW$(8242), "<special id="" 77""="">") 'Foot Mark
origString = Replace$(origString, ChrW$(8304), "<special id="" 74""="">") 'Degree
origString = Replace$(origString, ChrW$(8482), "<special id="" 24""="">") 'Trademark
origString = Replace$(origString, ChrW$(8486), "<special id="" 11""="">") 'Omega
origString = Replace$(origString, ChrW$(8594), "<special id="" 229""="">") 'Arrow Rt.
origString = Replace$(origString, ChrW$(8709), "<special id="" 1""="">") 'Phi Capital
origString = Replace$(origString, ChrW$(8710), "<special id="" 90""="">") 'Differenetial
origString = Replace$(origString, ChrW$(8725), "<special id="" 223""="">") 'Fraction Slash
origString = Replace$(origString, ChrW$(8730), "<special id="" 21""="">") 'Square-root
origString = Replace$(origString, ChrW$(8734), "<special id="" 3""="">") 'Infinity
origString = Replace$(origString, ChrW$(8776), "<special id="" 63""="">") 'Approx. Equal
origString = Replace$(origString, ChrW$(8800), "<special id="" 10""="">") 'Not Equal
origString = Replace$(origString, ChrW$(8804), "<special id="" 5""="">") 'Less than or equal
origString = Replace$(origString, ChrW$(8805), "<special id="" 79""="">") 'Greater than or equal
origString = Replace$(origString, ChrW$(9633), "<special id="" 81""="">") 'Box
origString = Replace$(origString, ChrW$(9642), "<special id="" 234""="">") 'Square BLK
origString = Replace$(origString, ChrW$(12289), ",")
origString = Replace$(origString, ChrW$(-97), "<special id="" 74""="">") 'Degree
origString = Replace$(origString, ChrW$(8451), "<special id="" 74""="">C") 'DegreeC
origString = Replace$(origString, ChrW$(-162), "<special id="" 22""="">") 'Tilde
origString = Replace$(origString, ChrW$(-257), "")
origString = Replace$(origString, ChrW$(-1279), "")
origString = Replace$(origString, ChrW$(-3937), "<special id="" 81""="">") 'Box
origString = Replace$(origString, ChrW$(168), "")
origString = Replace$(origString, ChrW$(212), "")
origString = Replace$(origString, ChrW$(173), "-")
origString = Replace$(origString, ChrW$(-248), "(")
For x = 1 To Len(origString)
xStr = Mid(origString, x, 1)
xCode = AscW(xStr)
If xCode > 127 Or xCode < 32 Then Stop
Next x
Cells(r, c).value = origString
End If
Next c
Next r
End Sub
Sub removeBox()
Dim shSource As Worksheet
Dim x, lrow As Long
Set shSource = ActiveSheet
lrow = shSource.Cells(Rows.count, 1).End(xlUp).row
For x = 1 To lrow
If AscW(Left(shSource.Cells(x, 1).value, 1)) = 1 Then
shSource.Cells(x, 1).NumberFormat = "@"
shSource.Cells(x, 1).value = Right(shSource.Cells(x, 1).value, Len(shSource.Cells(x, 1).value) - 1)
End If
Next x
End Sub
<tbody>
</tbody>
</special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></fraction></fraction></fraction></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special>
What happens is we have rows of data and for whatever reason a value is not needed any longer in Column B and yet there might be 10-100 more rows and those still need to be scrubbed for all the other columns of data. My cheating work around is to copy one of the top rows to the very last row in the file. I just wish I did not have to continue to do this.
Below is the macro and sample of a spreadsheet
Can someone help me get this to run to the last line of column A every time?
thanks
Joanie
Sub cleanAttributes()
'********************************************************************************
'* This macro will replace special characters with the Agility special
'* character tag.
'* It will also trim off any extra white-space characters at the beginning or
'* end of the text.
'* As written, this macro is expecting the text to be modified to be in Column B
'********************************************************************************
Dim lrow, lcol As Long
Dim x, r, c As Long
Dim xCode As Long
Dim origString As String
lrow = Cells(Rows.count, 2).End(xlUp).row
lcol = Cells(1, Columns.count).End(xlToLeft).Column
For x = 2 To lcol
Columns(x).NumberFormat = "@"
Next x
For r = 2 To lrow
For c = 2 To lcol
If Cells(r, c).value <> "" Then
origString = Cells(r, c).Text
origString = Trim(origString)
' Anything less than 32, remove
For I = 1 To 9
origString = Replace$(origString, ChrW$(I), "")
Next I
origString = Replace$(origString, ChrW$(13) & ChrW$(10), "<special id="" 70""="">") '
origString = Replace$(origString, ChrW$(13), "<special id="" 70""="">") '
origString = Replace$(origString, ChrW$(10), "<special id="" 70""="">") '
origString = Replace$(origString, ChrW$(11), "")
origString = Replace$(origString, ChrW$(12), "")
For I = 14 To 31
origString = Replace$(origString, ChrW$(I), "")
Next I
origString = Replace$(origString, ChrW$(37), "<special id="" 15""="">") '%
origString = Replace$(origString, ChrW$(38), "<special id="" 64""="">") '&
origString = Replace$(origString, ChrW$(64), "<special id="" 65""="">") '@
origString = Replace$(origString, ChrW$(126), "<special id="" 22""="">") '~
' 128 to 159, remove
For I = 128 To 159
origString = Replace$(origString, ChrW$(I), "")
Next I
origString = Replace$(origString, ChrW$(160), " ")
origString = Replace$(origString, ChrW$(161), "i")
origString = Replace$(origString, ChrW$(162), "<special id="" 68""="">") 'CENT
origString = Replace$(origString, ChrW$(163), "<special id="" 78""="">") 'Pound (money)
origString = Replace$(origString, ChrW$(164), "<special id="" 81""="">") 'Box
origString = Replace$(origString, ChrW$(165), "<special id="" 27""="">") 'Yen
origString = Replace$(origString, ChrW$(167), "<special id="" 19""="">") 'Section
origString = Replace$(origString, ChrW$(169), "<special id="" 69""="">") 'Copyright
origString = Replace$(origString, ChrW$(172), "")
origString = Replace$(origString, ChrW$(174), "<special id="" 18""="">") 'Registered Mark
origString = Replace$(origString, ChrW$(176), "<special id="" 74""="">") 'Degree
origString = Replace$(origString, ChrW$(177), "<special id="" 17""="">") 'Plus/Minus
origString = Replace$(origString, ChrW$(178), "<style id="" 8""="">2</style>") 'Square
origString = Replace$(origString, ChrW$(179), "<style id="" 8""="">3</style>") 'Cube
origString = Replace$(origString, ChrW$(180), "'")
origString = Replace$(origString, ChrW$(181), "<special id="" 8""="">") 'Micro
origString = Replace$(origString, ChrW$(182), "<special id="" 14""="">") 'Paragraph
origString = Replace$(origString, ChrW$(183), "<special id="" 25""="">") 'Bullet
origString = Replace$(origString, ChrW$(185), "")
origString = Replace$(origString, ChrW$(186), "<special id="" 230""="">") 'Ellipsis
origString = Replace$(origString, ChrW$(188), "<fraction d="" 4""="" n="" 1""="">") '1/4
origString = Replace$(origString, ChrW$(189), "<fraction d="" 2""="" n="" 1""="">") '1/2
origString = Replace$(origString, ChrW$(190), "<fraction d="" 4""="" n="" 3""="">") '3/4
origString = Replace$(origString, ChrW$(213), "'")
origString = Replace$(origString, ChrW$(214), "<special id="" 239""="">") 'O with umlaut
origString = Replace$(origString, ChrW$(215), "<special id="" 23""="">") 'Times Symbol
origString = Replace$(origString, ChrW$(216), "<special id="" 1""="">") 'Phi, Capital
origString = Replace$(origString, ChrW$(220), "<special id="" 26""="">") 'U with Umlaut
origString = Replace$(origString, ChrW$(233), "<special id="" 76""="">") 'E-accent
origString = Replace$(origString, ChrW$(247), "<special id="" 75""="">") 'Division Sign
origString = Replace$(origString, ChrW$(248), "<special id="" 2""="">") 'Phi, lower case
origString = Replace$(origString, ChrW$(934), "<special id="" 2""="">") 'Phi, lower case
origString = Replace$(origString, ChrW$(402), "<special id="" 219""="">") 'F latin
origString = Replace$(origString, ChrW$(650), "<special id="" 11""="">") 'Omega
origString = Replace$(origString, ChrW$(730), "<special id="" 74""="">") 'Degree
origString = Replace$(origString, ChrW$(778), "0<special id="" 74""="">") 'Degree
origString = Replace$(origString, ChrW$(913), "<special id="" 86""="">") 'Alpha
origString = Replace$(origString, ChrW$(915), "<special id="" 91""="">") 'Gamma
origString = Replace$(origString, ChrW$(916), "<special id="" 83""="">") 'Delta
origString = Replace$(origString, ChrW$(920), "<special id="" 93""="">") 'Theta
origString = Replace$(origString, ChrW$(923), "<special id="" 92""="">") 'Lambda
origString = Replace$(origString, ChrW$(931), "<special id="" 240""="">") 'Sigma
origString = Replace$(origString, ChrW$(937), "<special id="" 11""="">") 'Omega
origString = Replace$(origString, ChrW$(945), "<special id="" 225""="">") ' Alpha LC
origString = Replace$(origString, ChrW$(946), "<special id="" 20""="">") 'Beta
origString = Replace$(origString, ChrW$(947), "<special id="" 91""="">") 'Gamma
origString = Replace$(origString, ChrW$(949), "<special id="" 226""="">") 'Epsilon LC
origString = Replace$(origString, ChrW$(951), "<special id="" 227""="">") 'Eta LC
origString = Replace$(origString, ChrW$(952), "<special id="" 93""="">") 'Theta
origString = Replace$(origString, ChrW$(955), "<special id="" 92""="">") 'Lambda
origString = Replace$(origString, ChrW$(956), "<special id="" 8""="">") 'Micro
origString = Replace$(origString, ChrW$(960), "<special id="" 16""="">") 'Pi
origString = Replace$(origString, ChrW$(964), "<special id="" 224""="">") 'Tau LC
origString = Replace$(origString, ChrW$(8208), "<special id="" 9""="">") 'Minus
origString = Replace$(origString, ChrW$(8211), "<special id="" 9""="">") 'Minus
origString = Replace$(origString, ChrW$(8212), "<special id="" 7""="">") 'Long Dash
origString = Replace$(origString, ChrW$(8216), "<special id="" 13""="">") 'Quote Open Single
origString = Replace$(origString, ChrW$(8217), "<special id="" 71""="">") 'Quote Close Single
origString = Replace$(origString, ChrW$(8217), "<special id="" 82""="">") 'Apostrophe
origString = Replace$(origString, ChrW$(8220), "<special id="" 12""="">") 'Quotes Open Double
origString = Replace$(origString, ChrW$(8221), "<special id="" 4""="">") 'Inch Mark
origString = Replace$(origString, ChrW$(8221), "<special id="" 67""="">") 'Quotes Close Double
origString = Replace$(origString, ChrW$(8224), "<special id="" 72""="">") 'Dagger
origString = Replace$(origString, ChrW$(8225), "<special id="" 73""="">") 'Double Dagger
origString = Replace$(origString, ChrW$(8226), "<special id="" 25""="">") 'Bullet
origString = Replace$(origString, ChrW$(8230), "<special id="" 230""="">") 'Ellipsis
origString = Replace$(origString, ChrW$(8242), "<special id="" 77""="">") 'Foot Mark
origString = Replace$(origString, ChrW$(8304), "<special id="" 74""="">") 'Degree
origString = Replace$(origString, ChrW$(8482), "<special id="" 24""="">") 'Trademark
origString = Replace$(origString, ChrW$(8486), "<special id="" 11""="">") 'Omega
origString = Replace$(origString, ChrW$(8594), "<special id="" 229""="">") 'Arrow Rt.
origString = Replace$(origString, ChrW$(8709), "<special id="" 1""="">") 'Phi Capital
origString = Replace$(origString, ChrW$(8710), "<special id="" 90""="">") 'Differenetial
origString = Replace$(origString, ChrW$(8725), "<special id="" 223""="">") 'Fraction Slash
origString = Replace$(origString, ChrW$(8730), "<special id="" 21""="">") 'Square-root
origString = Replace$(origString, ChrW$(8734), "<special id="" 3""="">") 'Infinity
origString = Replace$(origString, ChrW$(8776), "<special id="" 63""="">") 'Approx. Equal
origString = Replace$(origString, ChrW$(8800), "<special id="" 10""="">") 'Not Equal
origString = Replace$(origString, ChrW$(8804), "<special id="" 5""="">") 'Less than or equal
origString = Replace$(origString, ChrW$(8805), "<special id="" 79""="">") 'Greater than or equal
origString = Replace$(origString, ChrW$(9633), "<special id="" 81""="">") 'Box
origString = Replace$(origString, ChrW$(9642), "<special id="" 234""="">") 'Square BLK
origString = Replace$(origString, ChrW$(12289), ",")
origString = Replace$(origString, ChrW$(-97), "<special id="" 74""="">") 'Degree
origString = Replace$(origString, ChrW$(8451), "<special id="" 74""="">C") 'DegreeC
origString = Replace$(origString, ChrW$(-162), "<special id="" 22""="">") 'Tilde
origString = Replace$(origString, ChrW$(-257), "")
origString = Replace$(origString, ChrW$(-1279), "")
origString = Replace$(origString, ChrW$(-3937), "<special id="" 81""="">") 'Box
origString = Replace$(origString, ChrW$(168), "")
origString = Replace$(origString, ChrW$(212), "")
origString = Replace$(origString, ChrW$(173), "-")
origString = Replace$(origString, ChrW$(-248), "(")
For x = 1 To Len(origString)
xStr = Mid(origString, x, 1)
xCode = AscW(xStr)
If xCode > 127 Or xCode < 32 Then Stop
Next x
Cells(r, c).value = origString
End If
Next c
Next r
End Sub
Sub removeBox()
Dim shSource As Worksheet
Dim x, lrow As Long
Set shSource = ActiveSheet
lrow = shSource.Cells(Rows.count, 1).End(xlUp).row
For x = 1 To lrow
If AscW(Left(shSource.Cells(x, 1).value, 1)) = 1 Then
shSource.Cells(x, 1).NumberFormat = "@"
shSource.Cells(x, 1).value = Right(shSource.Cells(x, 1).value, Len(shSource.Cells(x, 1).value) - 1)
End If
Next x
End Sub
<tbody> </tbody> | |||||||||||||||||||||||||||||||||||||||||||||||||||
<tbody>
</tbody>
</special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></fraction></fraction></fraction></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special></special>