delete row if no value

[shadow12345]

Hi,

I need a macro that looks at coloumn a:F of a work sheet and check that they ALL have a value, if not then deletes the whole row

 

[al_b_cnu]

Hi, this code works on Sheet1:

Sub DelRows()
Dim bDel As Boolean
Dim iCol As Integer
Dim lRow As Long, lRowEnd As Long
Dim R As Range
Dim WS As Worksheet

Set WS = Sheets("Sheet1")
lRowEnd = WS.UsedRange.Row + WS.UsedRange.Rows.Count - 1
For lRow = lRowEnd To 1 Step -1
    bDel = True
    For iCol = 1 To 6
        If IsNumeric(WS.Cells(lRow, iCol).Text) = True Then
            bDel = False
            Exit For
        End If
    Next iCol
    If bDel = True Then WS.Rows(lRow).Delete shift:=xlUp
Next lRow
End Sub

 

[erik.van.geit]

Hi, guys!
if you need a lot of rows to clear then this is fast without loop
step through the code with function key F8 to see how it works

Option Explicit

Sub Delete_Rows_COUNTA()
'Erik Van Geit
'051124
'if no data in all columns (FC to LC) then delete the row

Dim RNG As Range
Dim LR As Long

Const FR As Long = 2        'first row with data to check
Const FC As Integer = 1     'first column
Const LC As Integer = 6     'last column

LR = Cells.Find("*", [A1], xlFormulas, xlPart, xlByRows, xlPrevious, False, False).Row

Application.ScreenUpdating = False
Columns(1).EntireColumn.Insert

With Range(Cells(FR, 1), Cells(LR, 1))
    .FormulaR1C1 = "=IF(COUNTA(RC" & FC + 1 & ":RC" & LC + 1 & ")<" & LC - FC + 1 & ","""",1)"
    .EntireRow.Sort Key1:=.Cells(1), Order1:=xlAscending, Header:=xlNo
    On Error Resume Next
    .SpecialCells(xlCellTypeFormulas, 2).EntireRow.Delete
    On Error GoTo 0
    .EntireColumn.Delete
End With
Application.ScreenUpdating = True
End Sub

EDITS
can be made on top of the code, unless you really need another condition-type
the main difficulty in elaborating the code - setting this condition - resides in editing this line
.FormulaR1C1 = "=IF(COUNTA(RC" & FC + 1 & ":RC" & LC + 1 & ")<" & LC - FC + 1 & ","""",1)"

PROCEDURE
1. record a macro when one column inserted
2. you get: Selection.FormulaR1C1 = "=IF(COUNTA(RC[1]:RC[6])<6,"""",1)"
3. use "string-edit-logic" to change the formula, always taking care to replace a value with " & whatever & "
examples
"=IF(COUNTA(RC" & whatevera & ":RC[6])<6,"""",1)"
"=IF(COUNTA(RC" & whatevera & ":RC[6])<" & whateverb & ","""",1)"

kind regards,
Erik