Delete rows with counta < 13

MegloManiac

New Member
Joined
Jan 5, 2010
Messages
23
Hello,

I have a automated weekly report from our ERP which we copy into excel. The data is not yet ready for proper analysis. Most of the steps I can record in a macro except for one. I want to remove all rows where the counta < 13 for the columns A through N. I can do this by adding the formula =counta(A2:N2) and filling this for all rows and then filter on <13 and delete those rows, but I am sure there is a better way to build this in VBA only I don't how :) It should check from row 2 until the last filled line (which is variable per week)

thanks!
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
Sub test()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
    If WorksheetFunction.CountA(Range("A" & i).Resize(, 14)) < 13 Then Rows(i).Delete
Next i
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,108,630
Messages
5,523,989
Members
409,553
Latest member
alscno

This Week's Hot Topics

Top