If statement to delete rows ending at last row

smu04sew · #1 (**permalink**) 07-14-09, 04:20

Morning

I need to analyse the rows within data, where the number of rows vary each time.
I need a code which basically says:

if the row is the last row, then stop
else if the cell in column G of that row is empty, delete row
else do nothing

I have various bits of code in order to find the Last Row, so defining that isn't a
problem, i'm just unsure of how to do the rest of it.

Thanks

Kafrin · #2 (**permalink**) 07-14-09, 06:27

OK, so you find the last row with your existing code. I'm going to assume here that you're finding the row number, LastRow. Then you need to work down the rows above that. Try something like:

Dim C as Range, D as Range, ws as Worksheet

Set ws as Workbooks("MyWorkbook").Sheets("MySheet")
Set C = ws.Range("A2") 'Working down column A, assuming first row is header row

Do Until C.Row = LastRow-1

Set D = C
Set C = C.Offset(1,0) 'move C down before delete, otherwise will lose place when delete
If IsEmpty(ws.Cells(D.Row,8)) Then 'column G is the 8th column
D.EntireRow.Delete
LastRow = LastRow - 1 'Because your last row is now higher up, otherwise you end up endlessly deleting the rows below your data.
End If

Loop

Colin Legg · #3 (**permalink**) 07-14-09, 16:34

Another way without looping...

Change Sheet1 to the codename of your worksheet.

Code:

Sub Example1()
    Dim lLastRow As Long
    Dim rngToCheck As Range

    Application.ScreenUpdating = False

    With Sheet1
        'if the sheet is empty then exit...
        If Application.CountA(.Cells) = 0 Then Exit Sub

        'find the last row in the worksheet
        lLastRow = Get_Last_Row(.Cells)

        Set rngToCheck = .Range(.Cells(1, "g"), .Cells(lLastRow, "g"))
    End With
    
    If rngToCheck.Count > 1 Then
        'if there are no blank cells then there will be an error
        On Error Resume Next
        rngToCheck.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        On Error GoTo 0
    Else
        If IsEmpty(rngToCheck) Then rngToCheck.EntireRow.Delete
    End If
    
    Application.ScreenUpdating = True
End Sub


Public Function Get_Last_Row(ByRef rngToCheck As Range) As Long

    Dim rngLast As Range
    
    Set rngLast = rngToCheck.Find(what:="*", searchorder:=xlByRows, searchdirection:=xlPrevious)
    
    If rngLast Is Nothing Then
        Get_Last_Row = rngToCheck.Row
    Else
        Get_Last_Row = rngLast.Row
    End If
    
End Function

By the way, if you do use a loop to delete the rows, it's easier to start at the bottom and work upwards, thereby negating the deletion row-shift effect.

Hope that helps...

Internet Services

Web Development

Digital Marketing

Consumer Tech