VBA Question - Formatting External System Data Resulting In Circular References

[DR RENO]

New to VBA and trying to embed code in control button, but instead of getting data in correct format so that I can apply lookup formulas, it appears it's just generating circular references with no changes to my source data. HELP

Here is the code I'm using:

Private Sub CommandButton1_Click()
Dim X As Long, S As String, CodesToClean As Variant, Cell As Range
CodesToClean = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 127, 129, 141, 143, 144, 157)
For Each Cell In Selection
S = Replace(Cell.Value, Chr(160), " ")
For X = LBound(CodesToClean) To UBound(CodesToClean)
If InStr(S, Chr(CodesToClean(X))) Then S = Replace(S, Chr(CodesToClean(X)), "")
Next
Cell.Value = Replace(Replace(Application.Trim(Replace(Replace(Replace(Replace(Application.Trim(S), vbLf & " ", vbLf), " " & vbLf, vbLf), " ", Chr$(175)), vbLf, " ")), " ", vbLf), Chr$(175), " ")
Next
End Sub

 

[DR RENO]

Yes I expanded the range when I came across another data extract. I need to read up on dynamic ranges which would save me run-time. You're right on the array, but since this macro will be used for multiple data sources I kept in all the codes that I came across through research.

Figured out how to solve my other issue. Really appreciate your guys help on here. I wish I had your level of knowledge on VBA. Hopefully someday

 

[Michael M]

OK, lets just say you needed to check cells from A2:M ...and last row in column
you could then create a variable

Dim lr as long
lr = cells(rows.count,"M").end(xlup).row
for each cell in range ("A2:M" & lr)

Which would then change as your row requirement changed !

HTH

 

[DR RENO]

You just saved me about 30-seconds of run-time. Thank you for the recommendation Michael. It worked perfectly!

 

[Michael M]

In that case, also try adding

Private Sub CommandButton1_Click()  
  Dim X As Long, S As String, CodesToClean As Variant, Cell As Range, lr as long
  CodesToClean = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 127, 129, 141, 143, 144, 157)
[color=red]application.screenupdating=false[/color]
lr = cells(rows.count,"M").end(xlup).row
For Each Cell In range ("A2:M" & lr)
    S = Replace(Cell.Value, Chr(160), " ")
    For X = LBound(CodesToClean) To UBound(CodesToClean)
      If InStr(S, Chr(CodesToClean(X))) Then S = Replace(S, Chr(CodesToClean(X)), "")
    Next x
    Cell.Value = Replace(Replace(Application.Trim(Replace(Replace(Replace(Replace(Application.Trim(S), vbLf & " ", vbLf), " " & vbLf, vbLf), " ", Chr$(175)), vbLf, " ")), " ", vbLf), Chr$(175), " ")
  Next cell
  [color=red]application.screenupdating=true[/color]
 End Sub

 

[DR RENO]

That helped immensely Michael. 4800 rows processed in just a few seconds. Thanks again for your help. Have a Merry Christmas!

 

[Michael M]

Cheers mate...