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

    Unanswered: If statement to delete rows ending at last row

    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

  2. #2
    Join Date
    Apr 2009
    Posts
    14
    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

  3. #3
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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...

Posting Permissions

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