delete rows in worksheet if columns A to J are empty

  • Re: delete rows in worksheet if columns A to J are empty


    Hi Juergen


    Use

  • Re: delete rows in worksheet if columns A to J are empty


    Does the code actually look in the range columns A to J? I think it looks if the entire row has no values, then delete it.


    My aim is that if there is no data in this range -->delete entire row.
    If there is data in the range cells -- go to next row.


    It can be that e.g. in column K or L, there is actually data in the cells, but the code should just ignore them and look at A to J, if there is any data.


    Cheers
    Juergen

  • Re: delete rows in worksheet if columns A to J are empty


    You are right it does check the entire row for being empty.
    Try this revision[vba]Sub x()
    Dim rRange As Range
    Dim lrow As Long
    Dim rTest As Range

    Set rRange = Range("A1", Range("A65536").End(xlUp))
    With rRange
    For lrow = rRange.Cells.Count To 1 Step -1
    Set rTest = rRange.Cells(lrow, 1).Resize(1, 10)
    If WorksheetFunction.CountA(rTest) = 0 Then
    .Rows(lrow).EntireRow.Delete
    End If
    Next lrow
    End With


    End Sub[/vba]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!