Delete rows that contain a special character in the text

When cleaning data or manipulating a dataset in order to upload into an e-commerce website for instance, the data must be absolutely spot on, accurate and adhere to the websites data standards. This might mean that data with special characters such as !”$%^&*()_+ for instance may not be allowed within the data. This could cause problems if your dataset is large, or for that matter even if the dataset is not so large, detecting special characters in a batch of data is very difficult to do manually.

This example code is very short but immensely powerful in removing the row of data in a dataset that contains special characters. The example excludes the checking of some special characters in order to show how some such as the £ can be excluded where necessary. The code can be amended to suit your needs, and should be quite straightforward how it deals with the checking of the text and carrying out an event.

Private Sub Remove_rows_that_contain_spec_chars
'Removes rows of data that contain special characters (with exceptions possible)
For n = 10 To 1 Step -1
X = ActiveSheet.Range("A" & n)
Dim output As String
Dim c 'since char type does not exist in vba, we have to use variant type.
For i = 1 To Len(X)
c = Mid(X, i, 1) 'Select the character at the i position
If (c >= "a" And c <= "z") Or (c >= "0" And c <= "9") Or (c >= "A" And c <= "Z") _ Or c = " " Or c = "£" Or c = "." Or c = "," Then d = 0 Else ActiveSheet.Range("A" & n).EntireRow.Delete shift:=xlUp Exit For End If Next i Next n End Sub

Contact us for some advice and guidance on how your Excel development could be created and start helping your business straight away. Contact Us


Leave a Reply

Your email address will not be published. Required fields are marked *