Removing #Name Entries

[Trevor G]

I have the following code to clean a download CSV file, but it is leaving entries that has #Name which then requries a manual filter. How can I run the same code to remove these lines. The code I use is shown below@

Sub strip2()
'*********************************************
'The following code was created by Trevor G 31 March 2010
'Email
'The following code will strip away any text entries
'Or blank rows in the database extract
'Then it will convert the numbers in the MI Reference Column (A)
'Into A Number format
'*********************************************
Dim lngRow As Long
Range("K1048576").Select 'Range("k65536").Select
ActiveCell.End(xlUp).Select
lngRow = ActiveCell.Row + 1
Range("A2").Select
Do Until ActiveCell.Row = lngRow
If Not IsNumeric(ActiveCell.Value) Or ActiveCell.Value = "" Then
ActiveCell.EntireRow.Delete
lngRow = lngRow - 1
Else
ActiveCell.Offset(1, 0).Select
End If
Loop

End Sub

 

[Peter_SSs]

1. Selecting is rarely needed in vba and can slow your code considerably.

2. Looping through worksheet cells can also be quite slow.

Try this alternative approach in a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> strip3()<br>    <SPAN style="color:#00007F">Dim</SPAN> LR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    LR = Range("K" & Rows.Count).End(xlUp).Row<br>    Columns("A").Insert<br>    <SPAN style="color:#00007F">With</SPAN> Range("A2:A" & LR)<br>        .Formula = "=IF(ISNUMBER(B2),1,"""")"<br>        .Value = .Value<br>        .EntireRow.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo, _<br>            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _<br>            DataOption1:=xlSortNormal<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Columns("A").Delete<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

 

[Trevor G]

Peter thank you very much for looking at my thread and suggesting a solution. I had to leave work quickly (from London) to return home due to family crisis and checked it out on the train back to Tamworth.

A Much quicker method than the approach I was taking.

I am very grateful to you. This will be implemented on my return.