Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2009

    Unanswered: If statement to delete rows ending at last row


    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.


  2. #2
    Join Date
    Apr 2009
    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
    LastRow = LastRow - 1 'Because your last row is now higher up, otherwise you end up endlessly deleting the rows below your data.
    End If


  3. #3
    Join Date
    Sep 2008
    London, UK
    Another way without looping...

    Change Sheet1 to the codename of your worksheet.

    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
            On Error GoTo 0
            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
            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...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts