Delete Row if cell contains text

[Borborb]

I have found several difference renditions close to what I am trying to do but it isn't working, so I figured it was time to ask.

I have a text file that I am importing into Excel. It then has approximately 100,000 lines and much of it is useless, and needs to be reduced. I use import manager to eliminate the columns to 2.

There is a variety of data that needs to be deleted.
1. I search by blank cells in A and delete row
2. I search by blank cells in B and delete row
3. I have to CTRL+F ALL the following (This is the beginning of various entries, essentially add * to all)

• Ac
• No
• Inv
• Esc
• To
• Rep
• Ban

In reality, I need to delete every row where the value of the cell in A is not a number. The issue I have found with record macro, it doesn't like CTRL+F.

All said and done, if I manually clean this up, I'm left with about 11k lines.

I have attempted several options I have found on this site, and others, and keep coming up short. Any help would be greatly appreciated!

 

[Steve_R]

Highlight column A
Click Find and Select
Click Goto special
Click Constants
Uncheck Numbers
Click OK

All non-Numeric values should be highlighted.

Click Delete

 

[hiker95]

Borborb,

Welcome to the MrExcel forum.

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?

3. Can we see examples of what is in columns A, and, B?


I would like more information. Please see the Forum Use Guidelines in the following link:

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html


See reply #2 at the next link, if you want to show small screenshots.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729


If you are not able to provide screenshots, then:

You can post your workbook/worksheets to the following free site (sensitive data changed), and provide us with a link to your workbook:

https://dropbox.com

 

[My Aswer Is This]

Try This:

Sub Auto_Filter_Out_Non_Numeric()
Application.ScreenUpdating = False
Dim r As Range
Set r = Range("A1", Range("A" & Rows.Count).End(xlUp))
r.AutoFilter Field:=1, Criteria1:="=*"
r.Rows("2:" & r.Rows.Count).Delete
r.AutoFilter
Application.ScreenUpdating = False
End Sub

 

[Rick Rothstein]

Try This:

Sub Auto_Filter_Out_Non_Numeric()
Application.ScreenUpdating = False
Dim r As Range
Set r = Range("A1", Range("A" & Rows.Count).End(xlUp))
r.AutoFilter Field:=1, Criteria1:="=*"
r.Rows("2:" & r.Rows.Count).Delete
r.AutoFilter
Application.ScreenUpdating = False
End Sub

Does your code delete rows where cells in Columns A or B are blank?

Given the OPs method of loading the data, I am assuming there is no header row. Given that, for those into such thing, this can be done using a single line of code, albeit a rather long one, provided the OP is using XL2010 or greater (because of the number of rows of data)...

[table="width: 500"]
[tr]
	[td]Sub DeleteBlanksInColumnsAandB_DeleteNonNumbersInColumnA()
  Intersect(Columns("A"), Union(Columns("A").SpecialCells(xlConstants, xlTextValues Or xlErrors Or xlLogical), Columns("A:B").SpecialCells(xlBlanks))).EntireRow.Delete
End Sub[/td]
[/tr]
[/table]

 

[Rick Rothstein]

[table="width: 500"]
[tr]
	[td]Sub DeleteBlanksInColumnsAandB_DeleteNonNumbersInColumnA()
  Intersect(Columns("A"), Union(Columns("A").SpecialCells(xlConstants, xlTextValues Or xlErrors Or xlLogical), Columns("A:B").SpecialCells(xlBlanks))).EntireRow.Delete
End Sub[/td]
[/tr]
[/table]

The above code needs a minor modification...

[table="width: 500"]
[tr]
	[td]Sub DeleteBlanksInColumnsAandB_DeleteNonNumbersInColumnA()
  Intersect(Columns("A"), Union(Columns("A").SpecialCells(xlConstants, xlTextValues Or xlErrors Or xlLogical), Columns("A:B").SpecialCells(xlBlanks).EntireRow)).EntireRow.Delete
End Sub[/td]
[/tr]
[/table]

 

[My Aswer Is This]

Trying to get away from loops. To delete all rows where A is empty or non numeric I figure this will work.

Sub Auto_Filter_Out_Non_Numeric()
'Version 2
Application.ScreenUpdating = False
Dim r As Range
Set r = Range("A1", Range("A" & Rows.Count).End(xlUp))
r.AutoFilter Field:=1, Criteria1:=Array("*", ""), Operator:=xlFilterValues
r.Rows("2:" & r.Rows.Count).Delete
r.AutoFilter
Application.ScreenUpdating = False
End Sub

 

[Borborb]

This did exactly what I needed, thank you. I think the biggest problem was searching Google. It was rather hard to articulate what I was trying to accomplish, but learned another aspect of an already useful tool that I use, the advanced search.

While the macros way still work, this eliminates everything but the blank rows, which I can then filter out in 1 more step.

This is definitely the quickest and simplest solution!

 

[Borborb]

Highlight column A
Click Find and Select
Click Goto special
Click Constants
Uncheck Numbers
Click OK

All non-Numeric values should be highlighted.

Click Delete

This, rather, did it exactly as needed! Thanks Steve R

 

[Rick Rothstein]

While the macros way still work, this eliminates everything but the blank rows, which I can then filter out in 1 more step.

Did you miss the code I posted in Message #6? That single line of code handles both, the blanks in Column A and B as well as non-numbers in Column A.