Results 1 to 2 of 2

Thread: Best way to break on Null fields?

  1. #1

    Best way to break on Null fields?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello for anyone willing to help me with this application, I would greatly appreciate it. I am not sure..., well I am sure it is not the best way, since it does not work, but what I have is a loop which tests two consecutive rows (field two) for them if equal. If they are equal I want to delete one row and move on. The code works OK until the end when each field equals Null. For some reason, even thought the watch point says the fields are empty, I cannot get a break from the loop.

    Please advise as to my ignorance.......Thank you all

    For y = 1 To 211
    WR = Cells(y, 2)
    WT = Cells(y + 1, 2)
    If WR = WT Then
    Cells(y + 1, 2).Select
    Selection.EntireRow.Delete 'they are equal delete row
    If WR = Null And WT = Null Then ' test if both fields are null and break
    GoTo Break
    End If
    y = y - 2
    Else ' not equal move on
    y = y - 1
    End If
    y = y + 1
    Next y

    Break:

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    718
    Articles
    0
    Excel Version
    Excel 2010 64bit
    A blank cell is Empty not Null so test for length.

    To accomplish what you are asking change
    Code:
    If WR = Null And WT = Null Then ' test if both fields are null and break
    GoTo Break
    End If
    to
    Code:
    If Len(WR) = 0 And Len(WT) = 0 Then GoTo Break

Posting Permissions

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