Results 1 to 3 of 3

Thread: Query on the Ubound function

  1. #1

    Query on the Ubound function

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

    Hi All,

    I am a novice to intermediate level VBA user. I had started by recording macros and then studying them and I am now at a level where I can write intermediate difficulty level macros given some time.

    I am however having a hard time understanding the below code. "cols" has been declared as an long variable at the start of the code. Starscream, OptimusPrime...etc are column labels.

    What I dont understand is what the ubound function does here and especially the "To 0 Step -1" part.

    Would greatly appreciate if someone can explain so that it will add to my knowledge.

     cols = Split("Starscream, OptimusPrime,BuzzLighYear,CaptainKirk" & _
        ",Superman,Batman,Spiderman", ",")
    For i = UBound(cols) To 0 Step -1
    Set r = Rows(5).Find(cols(i), , , 1)
            If Not r Is Nothing Then
                n = n + 1
            End If
        If n > 0 Then Range(Cells(1, n + 1), Cells.SpecialCells(11)).EntireColumn.Delete

  2. #2
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Excel Version
    Ubound finds the upper boundary (hence the name ) of the specified array dimension (defaults to the first dimension). Here, the Split function returns a 7 element array with the dimension boundaries going from 0 to 6 and UBound returns the 6. It saves you needing to hardcode the number of elements into the code. Personally, I think the line should also use LBound:
        For i = UBound(cols) To LBound(cols) Step -1
    The Step -1 part is because the code is looping backwards through the array - i.e. it process the last element first, then element 5, then 4 and so on.
    This website wants to know your momentum - | Deny | | Allow |

  3. #3
    Thank you!!

    It makes sense to me now.

Posting Permissions

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