1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel Macro and Delete Row

Discussion in 'Business Applications' started by computerman29642, Oct 24, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    What is the best way to delete rows where both Column A & B are blank when the workbook is opened?

    I have come up with this so far. Is there a better way?

    Code:
    Sub test()
    Dim LastRow As Long
    LastRow = [A65536].End(xlUp).Row
    For i = LastRow To 1 Step -1
    If Cells(i, 1) = "" And Cells(i, 2) = "" Then
    Rows(i).Delete
    End If
    Next i
    End Sub
     
  2. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Is this in the workbook we've been working on? You can use this...
    Code:
        Range("A:B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete shift:=xlUp
    Be aware though, if this is in the workbook we've been working on, it will delete the first row as well, and that is because technically A1 doesn't have data, A1:C1, merged cells, have data. So you don't need a loop. If you don't want to delete row 1, you can use...
    Code:
        ws.Range("A2:B" & ws.Rows.Count).SpecialCells(xlCellTypeBlanks).EntireRow.Delete shift:=xlUp
     
  3. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Thanks, Zack. It is for the same workbook we have been working on.
     
  4. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Zack, I tried this code

    Code:
    ws.Range("A2:B" & ws.Rows.Count).SpecialCells(xlCellTypeBlanks
    
    I get the error message "Object Required".
     
  5. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    still have "i" in there? it is undefined....
     
  6. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    You missed part of your code while posting. The code is good though. Can you post your entire code again?
     
  7. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Here is the code that I have now

    Code:
    Public Sub DeleteRows()
        Dim ws As Worksheet
        Set ws = ActiveSheet
        ws.Range("A2:B" & ws.Rows.Count).SpecialCells(xlCellTypeBlanks).EntireRow.Delete shift:=xlUp
    End Sub
    
    The code works, but I need for it to only delete when both A & B Columns are empty. As of right now, one or the other can be empty and the row is deleted.
     
  8. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Ah, looking back I see I missed that. Had in my head 'either', sorry. For that you would need to do one of two things. 1) loop, or 2) add a helper column, add formula, delete criteria-met rows, delete helper column. Depending on your data and how big it might bet, the second option is generally better. But if it's only for a small data set, looping shouldn't be too bad at all. I would just change your original (if you wanted to go with the loop) ...
    Code:
    Sub test()
        Dim ws As Worksheet, i As Long
        For i = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row To 1 Step -1
            If ws.Cells(i, 1).Value = "" And ws.Cells(i, 2).Value = "" Then
                ws.Rows(i).Delete
            End If
        Next i
    End Sub
    And if you have worksheet events, you probably want to turn off events, so I would add ...
    Code:
    Sub test2()
        Dim ws As Worksheet, i As Long
        Call ToggleEvents(False)
        For i = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row To 1 Step -1
            If ws.Cells(i, 1).Value = "" And ws.Cells(i, 2).Value = "" Then
                ws.Rows(i).Delete
            End If
        Next i
        Call ToggleEvents(True)
    End Sub
    
    Public Sub ToggleEvents(blnState As Boolean)
    'Originally written by firefytr
        With Application
            .DisplayAlerts = blnState
            .EnableEvents = blnState
            .ScreenUpdating = blnState
            If blnState Then .CutCopyMode = False
            If blnState Then .StatusBar = False
        End With
    End Sub
     
  9. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Cman, I found this here and with a minor change (to protect A1 and that row) it appears to work fine:
    http://www.exceltip.com/st/Deleting_Empty_Rows/247.html
    Code:
    Public Sub DeleteBlankRows()
    Dim dbMaxRow As Double, dbMinRow As Double, i As Double
    Dim dbMaxCol As Double
    Dim rng As Range
    ActiveCell = A2
    On Error Resume Next
    
    'only look in used area of the worksheet where active cell is
    Set rng = Selection.Parent.UsedRange
    
    'calculate area to be searched for blank rows
    dbMaxRow = rng.Rows.Count '# of rows in used area
    dbMinRow = rng.Cells(1, 1).Row '1st row in used area
    dbMaxCol = rng.EntireColumn.Count '# of columns in used area
    
    For i = dbMaxRow To dbMinRow Step -1
    If IsError(rng.Cells(1, 1).Offset(i - 1, 0).EntireRow. _
    SpecialCells(xlCellTypeBlanks).Count) Then
    Else
    If rng.Cells(1, 1).Offset(i - 1, 0).EntireRow. _
    SpecialCells(xlCellTypeBlanks).Count = dbMaxCol Then
    rng.Cells(1, 1).Offset(i - 1, 0).EntireRow.Delete
    End If
    End If
    Next i
    
    Set rng = Nothing
    
    End Sub
     
  10. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Zack, I tried the second code you provided above, but I am getting an error message. Please see the attached workbook.
     
  11. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    You haven't set ws yet...
     
  12. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    I Think I need to go back home, get back in bed, and start all over again....LOL. Thanks Zack.
     
  13. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    LOL! No problem Chris. :)
     
  14. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Zack,

    I have added the set statement, and the code now runs. However, it does not delete the row even if both A & B Columns are empty.

    What is the difference in functionality of my original code

    Code:
    Sub test()
    Dim LastRow As Long
    LastRow = [A65536].End(xlUp).Row
    For i = LastRow To 1 Step -1
    If Cells(i, 1) = "" And Cells(i, 2) = "" Then
    Rows(i).Delete
    End If
    Next i
    End Sub
    
    and the code you just provided?

    Code:
    Sub test2()
        Dim ws As Worksheet, i As Long
        Set ws = ActiveSheet
        Call ToggleEvents(False)
        For i = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row To 1 Step -1
            If ws.Cells(i, 1).Value = "" And ws.Cells(i, 2).Value = "" Then
                ws.Rows(i).Delete
            End If
        Next i
        Call ToggleEvents(True)
    End Sub
    
    Public Sub ToggleEvents(blnState As Boolean)
    'Originally written by firefytr
        With Application
            .DisplayAlerts = blnState
            .EnableEvents = blnState
            .ScreenUpdating = blnState
            If blnState Then .CutCopyMode = False
            If blnState Then .StatusBar = False
        End With
    End Sub
    
     
  15. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Zack, what do you think of Gary L Brown's code (the one I pasted)?
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/762379

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice