Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2007
    Posts
    6

    Lightbulb Unanswered: Could someone enlighten me about "Option Compare Text"?

    I have this formatting macro that well deletes stuff, highlights a row based on certain arguments, etc. I know Option Compare Text makes a module case insensitive, I was wondering if this ability extends to the arguments for Nested Ifs & Cases w/in that module. Since I need to make the arguments case insensitive. Ex. If "Tax" = "TAX" = "tax", delete the entire row...

  2. #2
    Join Date
    Mar 2006
    Posts
    163
    Well I think the short answer is yes, that's the whole point of Option Compare Text.

  3. #3
    Join Date
    Feb 2004
    Posts
    533
    I have never used Option Compare Text for this purpose or any purpose. Using the text functions "uCase" or "lCase" are good way to avoid case problems when comparing text. Here's an example.

    Code:
    Public Sub checkRows()
    
    nRow = 25
    
    For i = nRow To 2 Step -1
        bMatch = (UCase(Cells(i, 1)) = UCase(Cells(i, 2)))
        If bMatch Then
            Rows(i).EntireRow.Delete
        End If
    Next
    
    End Sub
    Notice in the example I used 'Step -1' argument of the 'For' Loop to work backwards through the range. This way when a row is deleted it is not necessary to re-define the range because the subsequent row numbers have changed when the row has been deleted.

    Normally I would use a function or statement to assign the last row number of the used range to the variable 'nRow' however for this simple example I just assigned the variable a number.

    It doesn't make a difference if LCase or UCase funtion is used. The purpose is to ensure values are compared without a difference in case.
    ~

    Bill

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi Bill

    I tend to do exactly the same. The only time I've used Option Compare is when verifying passwords that are case sensitive.

    MTB

Posting Permissions

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