Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    delete rows macro (Excel 2000)

    I need to delete blank rows. Column A is used to number the rows in each section of the spreadsheet. Only the sum function is used in various columns. So this is the task: If Column B is blank and the result of the sum function is 0, then delete the row. I tried this macro and it didn't accomplish what I needed. Thank you.

    Sub deleteEmptyrows()
    Dim rowNum As Long
    Lastrow = ActiveSheet.UsedRange.row - 1 + _
    ActiveSheet.UsedRange.rows.Count
    Application.ScreenUpdating = False
    For rowNum = Lastrow To 1 Step -1
    If Application.CountA(rows(rowNum)) = 0 Then _
    rows(rowNum).delete
    Next rowNum
    End Sub

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: delete rows macro (Excel 2000)

    How do we test for:"the result of the sum function is 0"?

    I suspect changing your line to:
    If isempty(cells(rowNum,2)) and _
    vResult = 0 Then _
    rows(rowNum).delete

    You will have to give me a clue on how to define "vResult"

    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: delete rows macro (Excel 2000)

    The test for CountA = 0 tests that there are NO non-blank cells in a row. From your description, I gather that column A is always populated, so the count would never be 0. Cells with formulas don't count as blanks either. Can you try to describe more precisely what you want, and perhaps attach a small demo worksheet?

  4. #4
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: delete rows macro (Excel 2000)

    Here is a sample. I need to delete rows 22 - 30 and 39 - 69.

  5. #5
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: delete rows macro (Excel 2000)

    I think this is close to what you need...once you post an example I can help some more....
    Sub DeleteEmptyRows()

    Dim R As Long
    Dim C As Range
    Dim Rng As Range


    Range("B1").Select
    Do
    If Not (ActiveCell) = 0 Then
    ActiveCell.Offset(1, 0).Select
    End If
    Loop Until (ActiveCell) = 0


    If (ActiveCell) = 0 Then
    Set Rng = Selection
    Else
    Set Rng = ActiveSheet.UsedRange.Rows
    End If
    For R = Rng.Rows.Count To 1 Step -1
    If (ActiveCell) = 0 Then
    Rng.Rows®.EntireRow.Delete
    End If
    Next R
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: delete rows macro (Excel 2000)

    Why do you want to delete row 22 and 23, but not 31 and 32?
    Why do you want to delete row 39, but not 35 - 37?

  7. #7
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: delete rows macro (Excel 2000)

    If you change range B1 to M1 it will work, but you'd have to keep running the Macro (setting a macro button or something). I don't know how to loop it, and haven't the time to figure it out right now, sorry.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  8. #8
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: delete rows macro (Excel 2000)

    *hoping* That was a typo on her part...
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  9. #9
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: delete rows macro (Excel 2000)

    It was a typo. I was looking at Column A instead of the actual row number when I was writing the post.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: delete rows macro (Excel 2000)

    The question remains, why do you want to delete row 39 but not 35 - 37.

  11. #11
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: delete rows macro (Excel 2000)

    Try this - give it a second to run through....

    Function DeleteLoop()

    Range("M1").Select
    Do
    If Not (ActiveCell) = 0 Then
    ActiveCell.Offset(1, 0).Select
    End If
    Loop Until (ActiveCell) = 0

    Call DeleteEmptyRows
    End Function

    Sub DeleteEmptyRows()

    Dim R As Long
    Dim C As Range
    Dim Rng As Range

    If (ActiveCell) = 0 Then
    Set Rng = Selection
    Else
    Set Rng = ActiveSheet.UsedRange.Rows
    End If
    For R = Rng.Rows.Count To 1 Step -1
    If (ActiveCell) = 0 Then
    Rng.Rows®.EntireRow.Delete
    End If
    Next R

    Call DeleteLoop

    End Sub
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  12. #12
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: delete rows macro (Excel 2000)

    I was thinking she wanted to delete those without values in column M (companies with no purchases or whatever....in which case I just made a code that would work for me....) the following code would remove up to 500 instances of '0'. I'm still just learning, anyone who would like, please nitpick this code:

    Function DeleteEmptyRows()
    Dim i As Integer
    Dim intRowCount As Integer
    intRowCount = Range("B1").Select
    For i = 1 To 500

    Do
    If Not (ActiveCell) = 0 Then
    ActiveCell.Offset(1, 0).Select
    End If
    Loop Until (ActiveCell) = 0

    Dim R As Long
    Dim C As Range
    Dim Rng As Range

    If (ActiveCell) = 0 Then
    Set Rng = Selection
    Else
    Set Rng = ActiveSheet.UsedRange.Rows
    End If
    For R = Rng.Rows.Count To 1 Step -1
    If (ActiveCell) = 0 Then
    Rng.Rows®.EntireRow.Delete
    End If
    Next R

    Next i

    End Function
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  13. #13
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: delete rows macro (Excel 2000)

    If the OP wants to delete all rows with a number in Col A and a zero in Col M, then she would omit

    Cells(rowNum, 2).Value = "" And _

    in the code I posted.

    In Excel it's always better to Dim a row counter as Long, since the maximum number of rows in an Excel spreadsheets since ... XL95? ... is greater than an Integer Type can hold, though you are safe if you want to quit at 500 deletes. Also ' intRowCount = Range("B1").Select' means nothing, and it and some other variables do not appear to be used in your subsequent code. Finally, you don't need to activate a cell to operate on it; Activating slows the code down. The offset method is good to learn for use to iterate through a range.

    Editing your code -without testing-, this might be simpler:

    Function DeleteEmptyRows()
    Dim i As Integer
    Dim R As Long
    Dim Rng As Range

    Set Rng = ActiveSheet.UsedRange.Rows
    For R = Rng.Rows.Count To 1 Step -1
    If Offset(Rng, R, 13).Value = 0 Then ' see if the cell 13 columns right of the leftmost column in the Usedrange is zero
    Rng.Rows®.EntireRow.Delete
    i = i + 1 ' increment the counter for 500
    End If
    If i = 500 Then Exit For ' stop processing
    Next R

    Set Rng = Nothing ' empty the range object before the code ends
    End Function

    There's a whole lot more, for example, UsedRange has to be used carefully, as it may not return what you expect. In the above example, the code may be wrong if column A is not used.
    -John ... I float in liquid gardens
    UTC -7±DS

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: delete rows macro (Excel 2000)

    Jeremy: Have you actually tried your code? I haven't, but it does not look like it will work very well. First, your code does a lot of cell selecting and then working with the ActiveCell (like recorded macro code does). This is almost always NOT a good way to do things. First, it causes the screen to flash while the macro is running, and second it is very slow. If there are very many cells involved, the code will run a long time.

    Second, you have this loop in your code:

    <pre>For R = Rng.Rows.Count To 1 Step -1
    If (ActiveCell) = 0 Then
    Rng.Rows®.EntireRow.Delete
    End If
    Next R
    </pre>


    That code tests the ActiveCell to see if it is zero and then deletes a row if it is and does nothing if it is not. There is nothing in the loop to change the ActiveCell to a different cell. So, if the ActiveCell is not zero when the loop starts, nothing will be deleted since the ActiveCell will never change. If the ActiveCell is zero when the loop starts, then all rows will be deleted since the ActiveCell does not change and it will remain zero throughout the loop.
    Legare Coleman

  15. #15
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: delete rows macro (Excel 2000)

    First, to all, I appreciate the criticism, this is my 2nd day ever typing macros....in response to Legare's Comment;

    Do
    If Not (ActiveCell) = 0 Then
    ActiveCell.Offset(1, 0).Select
    End If
    Loop Until (ActiveCell) = 0

    My code works, I just don't have a variable for column b being blank.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

Page 1 of 2 12 LastLast

Posting Permissions

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