Delete incomplete/bad rows?

  • Thread starter Thread starter AF
  • Start date Start date
A

AF

I've a worksheet that imports data from a text file every
time it's opened. How do I automatically search the sheet
with the imported data for incomplete rows and delete
these rows?
Note: an incomplete row has less or more filled cells than
10 (some data is missing or junk is added to the row!)
 
Are you sure that is a valid criteria. What if 5 cells of data were missing, and 5 pieces of junk
got added to other blank cells - You still have 10 cells.

Anyway, taking that as your criteria, here is a tweak on one of Chip Pearson's for deleting blank
rows:-
(For deleting blank rows, just set the <>10 bit to =0)

Either select the range first, or it will simply default to the used range anyway.

Public Sub DeleteRows()
'Chip Pearson
'Will delete all rows that don't have 10 cells of data in them
Dim r As Long
Dim c As Range
Dim n As Long
Dim rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If Selection.Rows.Count > 1 Then
Set rng = Selection
Else
Set rng = ActiveSheet.UsedRange.Rows
End If
n = 0
For r = rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(rng.Rows(r).EntireRow) <> 10 Then
rng.Rows(r).EntireRow.Delete
n = n + 1
End If
Next r
EndMacro:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
 
Back
Top