Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Lounger
    Join Date
    Sep 2001
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reading Blank Data (English)

    How would I write a macro so that when it is checking the cells data, it will be able to read that the cell has no data and will delete the entire row?

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Reading Blank Data (English)

    Ximon

    Would that help? This would look for cells in Column A all the way down to the last row of your worksheet and if that cell is empty then it deletes the row.

    Sub DeleteBlankRows()
    Dim lCounter As Long

    With ActiveSheet
    For lCounter = 2 To .Rows.Count
    If Len(CStr(Range("A" & lCounter).Value)) = 0 Then
    .Rows(lCounter).Select
    '/Cell is blanck so delete row.
    .Rows(lCounter).Delete Shift:=xlUp
    End If
    Next lCounter
    End With
    End Sub

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Lounger
    Join Date
    Sep 2001
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reading Blank Data (English)

    Thanks for such a quick reply.

  4. #4
    Lounger
    Join Date
    Sep 2001
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reading Blank Data (English)

    Hi Wassim,

    I tried the macro you wrote for me and it works very good, but there is one thing. When I delete the row I notice that the data from the previous row has moved up a row and so I don't think I should be deleting entire rows. How would I only delete the data that is in the cells of colume B and C of the selected rowusing your example?

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

    Re: Reading Blank Data (English)

    Your routine will miss deleting rows if there are two in a row with empty cells.
    Legare Coleman

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

    Re: Reading Blank Data (English)

    I am not clear on what you want to do.

    It sounds like you want to delete the data from the cell but if there is no data in the cell, there is nothing there to delete.

    If a cell in Column B is empty, do you want to delete the cell and move the rest of the cells in column B up without affecting data that might be in other columns? If the cell in B is empty, do you want to delete the cell in column C even if it contains data, or do you want to only delete cells in column C if they are empty. What do you want to do if one of the cells is empty but the other is not?
    Legare Coleman

  7. #7
    Lounger
    Join Date
    Sep 2001
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reading Blank Data (English)

    Here is the situation. I have the two formulas in all the cells in column A and B. They both reference to the cells in column C. I want to delete the formulas in the cells of columns A and B that have cells in column C with no data. Is this possible? or am I just confusing you?

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Reading Blank Data (English)

    You could set your formulas up to return a blank if the contents of C is blank, <pre> =IF(Cx = "","",Formula),</pre>

    where x is the row number, and Formula is your original formula (without the = sign).

    Would that help ?

    Andrew C

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

    Re: Reading Blank Data (English)

    Ok, I think I am beginning to understand what you want to do. However, one more question before we proceed. Are you really just trying to keep a zero or error from displaying in columns A and B if C is empty, or do you really need to delete the formulas? You can keep the zero or error from displaying by using a simple formula, and then you don't have to reenter the formula if you later enter something in C. If the formula in A is =1/C1, which will produce an error is C is empty, then you can change the formula to:

    <pre>=IF(C1="","",1/C1)
    </pre>

    Legare Coleman

  10. #10
    Lounger
    Join Date
    Sep 2001
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reading Blank Data (English)

    I really need to delete the formula. I have to have the cells in column A and B to to empty.

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

    Re: Reading Blank Data (English)

    Sub ClearUp()
    Dim rngCell As Range
    For Each rngCell In Columns("C:C").SpecialCells(xlCellTypeBlanks)
    rngCell.Offset(0, -1).ClearContents
    rngCell.Offset(0, -2).ClearContents
    Next rngCell
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  12. #12
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Reading Blank Data (English)

    Ximon

    Try this version then:

    Sub DeleteBlankCells()
    Dim lCounter As Long

    With ActiveSheet
    For lCounter = 2 To .Rows.Count
    If Len(CStr(Range("A" & lCounter).Value)) = 0 Then
    '/Cell is blanck so delete row.
    .Range("B" & lCounter).Resize(1, 2).Delete Shift:=xlUp
    End If
    Next lCounter
    End With
    End Sub

    I also used the resize option here just to give you an example of how you can resize a range that you pick. But I am afraid that anytime you delete rows like that, the data may become skewed, that is the rows don't match. I don't know what the data you are working with looks like, but I need to caution you on this issue.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  13. #13
    Lounger
    Join Date
    Sep 2001
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reading Blank Data (English)

    Thanks I'll try it right away.

  14. #14
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Reading Blank Data (English)

    Legare

    You are right, I should not use a For-Next loop for this one. I was not sure on what the requierments were so i decided to provide a starting point. I guess I need to send another reply because a formula and a sort will be better and faster...

    Thanks

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

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

    Re: Reading Blank Data (English)

    I understood you didn't want the row deleted; if so you might try my contribution. Its only danger is that it will delete anything in columns A & B including text and headings, etc., if the cell in the corresponding row in column C is blank.
    -John ... I float in liquid gardens
    UTC -7ąDS

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
  •