This is a VBA code to remove the user IDs from the exported spreadsheet. Apply the following code and run the macro to remove all User IDs from the spreadsheet
Sub RemoveUserIDs()
'run against rows 1 to 10, if you want more change the start and end below
start_of_range = 2
end_of_range = ActiveSheet.UsedRange.Rows.Count
'currently changes cells in colum A if you want B change to 2, C to 3 etc etc
colum_start = 1
For col = start_of_range To ActiveSheet.UsedRange.Columns.Count
For t = start_of_range To end_of_range
newstring = ""
For i = 1 To Len(Cells(t, col))
If Not IsNumeric(Mid(Cells(t, col), i, 1)) And Mid(Cells(t, col), i, 1) <> "#" Then
newstring = newstring & _
Mid(Cells(t, col), i, 1)
End If
Next i
newstring = Replace(newstring, ";;", ", ")
newstring = Replace(newstring, ";", "")
Cells(t, col).Value = newstring
Next t
Next col
ActiveWorkbook.Save
End Sub
Hi, thanks for this post. I am not sure if anyone is still following the blog, but how do I change this macro to end at the 7th column? It runs across all of my columns and deletes numbers in my date fields.
ReplyDeleteThanks!
One issue I found is an error in the line:
ReplyDeleteFor col = start_of_range To ActiveSheet.UsedRange.Columns.Count
The looping through columns starts at the value set in start_of_range (which is the first ROW, I believe, in this case 2 so that it skips the header row) rather than column_start which is the variable defined for that purpose.
So changing that line to
For col = column_start To ActiveSheet.UsedRange.Columns.Count
fixes it.
In my case, I only wanted one column, so I actually just tweaked it to
For col = column_start To column_start
although with some small tweaking, you could remove the loop altogether. I opted to leave it in because it was a faster change and in case I want it over multiple columns in the future. Although I might set a column_end value instead of presuming it's supposed to go to the end of the spreadsheet.
Great. Thanks for sharing.
Delete