Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    IF-THEN with OR and AND (Excel xp)

    I am creating a macro to delete lines that are blank OR have a spaceband in COL A AND COL B

    Sub DeleteRowsEmpty()

    Range("A" & lLastRow).Select
    For i = lLastRow To 1 Step -1
    If Range("A" & i).Value = "" Or Range("A" & i).Value = " " _
    And Range("B" & i).Value = "" Or Range ("B" & i).value = " " Then
    Selection.EntireRow.Delete
    Selection.Offset(-1, 0).Select
    Else
    Selection.Offset(-1, 0).Select
    End If
    Next

    It is not working. It is still deleting the lines where i have a blank in Col A and text in Col B. I want it to skip these lines. Where am i going wrong? thank you for the help

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

    Re: IF-THEN with OR and AND (Excel xp)

    There are a couple of problems with your code. First, I don't see where lLastRow is ever set to a value. Second, you need some parenthesis in your If statement to tell it what order to do the Ands and Ors in.

    I think that the code below will do what you want, and without selecting the lines which slows the code down.

    <pre>Public Sub DeleteRowsEmpty()
    Dim lLastRow As Long, I As Long
    lLastRow = Worksheets("Sheet1").UsedRange.Row + Worksheets("Sheet1").UsedRange.Rows.Count - 1
    With Worksheets("Sheet1").Range("A1")
    For I = lLastRow To 0 Step -1
    If Trim(.Offset(I, 0) & .Offset(I, 1)) = "" Then
    .Offset(I, 0).EntireRow.Delete
    End If
    Next I
    End With
    End Sub
    </pre>

    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Leigh on Sea, Essex, England
    Posts
    263
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IF-THEN with OR and AND (Excel xp)

    It just goes to show that there are little gems buried all over this newsgroup.

    I actually read the post with a view to helping, and came away knowing all about the Offset method - and I dearly wish that I had known about this before! I spent ages writing code to calculate top lines and storing them in variables and then adding offsets to them (it was worse if I did it in 2D). This method is like a breath of fresh air. I'm beginning to learn that the With... command is really quite powerful, if used properly.

    Any more little bits and pieces I should know about? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Regards,

    Stuart

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

    Re: IF-THEN with OR and AND (Excel xp)

    There are lots of tid bits floating around in the Excel forum.
    Legare Coleman

Posting Permissions

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