PDA

View Full Version : [SOLVED] Delete row if cell is not a number



jaydee
09-13-2017, 12:13 PM
All-

I have a database export file with hundreds and thousands of rows that comes out poorly. Is it possible to delete all rows if by looking at column A, the cell does not equal a number?

I attached a sample, I want to keep the highlighted rows only which contain a four digit number.


Thank you,

mdmackillop
09-13-2017, 12:33 PM
Approx. how many rows?

Paul_Hossler
09-13-2017, 12:37 PM
Try this




Sub Macro2()
Application.ScreenUpdating = False
With ActiveSheet.UsedRange.Columns(1)
On Error Resume Next
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
.SpecialCells(xlCellTypeFormulas).EntireRow.Delete
.SpecialCells(xlCellTypeConstants, xlTextValues).EntireRow.Delete
.SpecialCells(xlCellTypeConstants, xlLogical).EntireRow.Delete
.SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
On Error Goto 0
End With
Application.ScreenUpdating = True

End Sub





20333

This deletes the headers in row 1, but if you want them, it's easily adjusted

mdmackillop
09-13-2017, 12:41 PM
For few rows

Sub Test()
lrw = Cells(Rows.Count, 1).End(xlUp).Row
For i = lrw To 1 Step -1
If Not Cells(i, 1).Formula Like "####" Then Rows(i).Delete
Next
End Sub

mdmackillop
09-13-2017, 12:44 PM
Hi Paul
3/5 figure numbers?

Paul_Hossler
09-13-2017, 01:10 PM
Hi Paul
3/5 figure numbers?

Well ..... sample only had 4 figure number, and OP said 4

If it's a concern, I'd whack a whole bunch of rows at a time (maybe adjusting to leave header row 1) using mine, and then loop / filter what left to keep between 1000 and 9999 using yours. "Like" is a relatively slow operator so a <1000 or >9999 test might likely be faster

My observation is that performance is better to delete a whole block of rows, instead of one at a time

Maybe we could put everything into an array :devil2:

jaydee
09-13-2017, 02:45 PM
Thanks guys, it worked like a charm! Both codes worked and were fast enough for me, I'm happy.
It's an old system and all of my reports come out that way so this saves me a LOT of time. I didn't need the headers since I just dump the data into another file.

Thank you again for your time and consideration! Have a great day guys, you are AWESOME