<div>
What is the 'special' character?
</div>


<div>
any character not a-z or 0-9
</div>


<div>
<pre><code class="code-10 nolinks" id="code-20-42435360-1"><span>Sub DeleteRows()</span>
<span>Dim lngLastRow As Long</span>
<span>Dim lngRow As Long</span>
<span></span>
<span>Application.ScreenUpdating = False</span>
<span>lngLastRow = Range(&quot;A1048576&quot;).End(xlUp).Row</span>
<span></span>
<span>For lngRow = lngLastRow To 1 Step -1</span>
<span>    Select Case Asc(Left(Cells(lngRow, &quot;A&quot;), 1))</span>
<span>        Case 48 To 57, 65 To 90, 97 To 122</span>
<span>            ' OK</span>
<span>            ' 0 to 9, A to Z, a to z</span>
<span>        Case Else</span>
<span>            Cells(lngRow, &quot;A&quot;).EntireRow.Delete</span>
<span>    End Select</span>
<span>Next</span>
<span>Application.ScreenUpdating = True</span>
<span>End Sub</span>
</code></pre>
</div>


<div>
If you only have single characters in column A then perhaps you could use this.<br />

<pre><code class="code-1 nolinks" id="code-20-42435373-1"><span>Dim I As Long</span>
<span></span>
<span>    For I = Range(&quot;A&quot; &amp; Rows.Count).End(xlUp).Row To 2 Step -1</span>
<span>        If Range(&quot;A&quot; &amp; I).Value Like &quot;[!0-9A-Za-z]&quot; Then</span>
<span>            Rows(I).Delete</span>
<span>        End If</span>
<span>    Next I</span>
</code></pre>
</div>


<div>
340k rows of data for comparison? I would write a .qry (Microsoft query) to generate the output instead.<br />
<br />
that could be faster than VBA codes to check row by row.
</div>


<div>
hi Martin, how long do you think it would take for that to run? still running 15 minutes later..
</div>


<div>
looks like it eliminated 40k rows, but still many rows left to eliminate . see attached<br />
<a href="https://filedb.experts-exchange.com/incoming/2018/01_w03/1220227/genderiee2.xlsm" target="_blank" class="file-inline" title="genderiee2.xlsm (5.6 MB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>genderiee2.xlsm</a>
</div>


genderiee2.xlsm

<div>
finnstone<br />
<br />
Do you actually want to delete entire rows or do you just want to clear column A of special characters?
</div>


<div>
You’re welcome and I’m glad I was able to help.<br />
<br />
If you expand the “Full Biography” section of <a href="https://www.experts-exchange.com/M_258447.html" rel="ugc">my profile</a> you’ll find links to some articles I’ve written that may interest you.<br />
<br />
Marty - Microsoft MVP 2009 to 2017<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Experts Exchange MVE 2015 <br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
</div>


<div>
martin, is it possible to edit this one? i would like to NOT delete cells that have the following special characters -<br />
<br />
&quot;<br />
,<br />
.<br />
:<br />
;<br />
(<br />
)<br />
&amp;<br />
!<br />
-
</div>


<div>
so really just going after the CRAZY special charactesr like chinese symobls and copyrtight symbols
</div>


<div>
<div class="content wysiwyg-content">
i have 340k rows. need to check value in COL A, if there is special character, delete that row<br />
<br />
thanks!
</div>
</div>


i have 340k rows. need to check value in COL A, if there is special character, delete that row

thanks!

delete row if special character in col A

Visual Basic for Applications (VBA) enables building user-defined functions (UDFs), automating processes and accessing Windows API and other low-level functionality through dynamic-link libraries (DLLs). VBA is closely related to Visual Basic and uses the Visual Basic Runtime Library, but it can normally only run code within a host application rather than as a standalone program. It can, however, be used to control one application from another via OLE Automation. VBA is built into most Microsoft Office applications.

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Microsoft Excel

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.