Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete Row Based On Certain Cell = Empty (2003)

    Greetings,

    I am sure this has been discussed, just did not know what keyword to search.

    I need to Remove rows of data based upon a cell having data or not. If cell = blank, then delete row. Will need to cycle through spreadsheet until done. The file will be updated peroidoically, then have the macro ran to remove the rows.



    Thanks,
    Brad

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

    Re: Delete Row Based On Certain Cell = Empty (2003)

    Should the macro look at a specific column within the row? If so, which one?
    Or should the row be deleted if there is any blank cell in the row?

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Row Based On Certain Cell = Empty (2003)

    Hans,
    Sorry, forgot the 'details' It should look at column Z to determine if the cell is empty or not. If in a particular Row, cell in position 'Z' is empty, then remove that row.



    Brad

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

    Re: Delete Row Based On Certain Cell = Empty (2003)

    Try this:
    <code>
    Sub DeleteRowsIfColumnZIsEmpty()
    Dim r As Long
    Dim n As Long
    n = Range("Z65536").End(xlUp).Row
    For r = n To 1 Step -1
    If Trim(Range("Z" & r)) = "" Then
    Range("Z" & r).EntireRow.Delete
    End If
    Next r
    End Sub</code>

  5. #5
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Row Based On Certain Cell = Empty (2003)

    Hans,

    Will try ASAP. Also, the converse of this : If the Cell is NOT empty, how do you indicate that? I assume the same code will work, with exception of the If Trim line.

    Just ran the below, and I guess I need to begin looking at the data from Row 6 and below. Forgot about the headers.


    Sub DeleteRowsIfColumnZIsEmpty()
    Dim r As Long
    Dim n As Long
    n = Range("Z65536").End(xlUp).Row
    For r = n To 1 Step -1
    If Trim(Range("Z" & r)) = "" Then
    Range("Z" & r).EntireRow.Delete
    End If
    Next r
    End Sub



    Thanks,
    Brad

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

    Re: Delete Row Based On Certain Cell = Empty (2003)

    Insert the word Not and a space before Trim.

  7. #7
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Row Based On Certain Cell = Empty (2003)

    Hans,


    I bet I was editing the post while you were responding....

    I added the issue about beginning the data review at row 6....


    Brad

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

    Re: Delete Row Based On Certain Cell = Empty (2003)

    You should be able to figure that out for yourself now. It's rather obvious what has to be changed if you don't want to continue to row 1.

  9. #9
    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 Row Based On Certain Cell = Empty (2003)

    To test if NOT Empty you can use:
    <pre>If Trim(Range("Z" & r)) <> "" Then
    'code for cell is not blank
    End If</pre>


    Or if you want to have code for both possibilities
    <pre>If Trim(Range("Z" & r)) = "" Then
    'Code for cell is blank
    Else
    'code for cell is not blank
    End If</pre>


  10. #10
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Row Based On Certain Cell = Empty (2003)

    Sorry, not obvious....


    I understand that it pertains to the line for the Range, but no, I do not see it.


    Brad

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

    Re: Delete Row Based On Certain Cell = Empty (2003)

    The number 1 occurs only once in the code. You have to change this to 6:

    For r = n To 1 Step -1

    becomes

    For r = n To 6 Step -1

    <img src=/S/heavy.gif border=0 alt=heavy width=40 height=34>

Posting Permissions

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