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,
Approx. how many rows?
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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
Capture.JPG
This deletes the headers in row 1, but if you want them, it's easily adjusted
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
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
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Hi Paul
3/5 figure numbers?
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Well ..... sample only had 4 figure number, and OP said 4Originally Posted by mdmackillop
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
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
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
Posting Permissions
Reply With Quote