Basic Or Statement and Range Question (used with Excel)

Posted on 2004-11-18
Last Modified: 2010-05-02
I have the following code to hide/unhide rows based on column A's cell value for a set of rows.  If the value matches the values in cells A2:A22 the rows should unhide and if it does not match they should be hidden.

Sub btn1_Click()

Dim LastRow As Long, i As Long
Dim variable

Application.ScreenUpdating = False
variable= Sheets("MySheet").Range("A2:A22").Value
LastRow = ActiveSheet.UsedRange.Rows.Count

For i = 28 To LastRow
If Cells(i, 1).Value <> variable Then
Rows(i).Hidden = True
Rows(i).Hidden = False
End If

Application.ScreenUpdating = True
End Sub

With the above code I am getting a type mismatch error.  I am new to VB and don't know if I need an array or if I am just making a very simple mistake.

I would also like to use the same code in another situation to unhide cells based on more than one criteria.  I thought I could replace this:
If Cells(i, 1).Value <> variable Then
If Cells(i, 1).Value <> variable or (variable + .1 ) Then
but that results in ALL of the rows being hidden (even the ones that should be shown).

Thank you in advance,
Question by:Gobernaster
    LVL 76

    Expert Comment

    You variable called variable is declared as a variant by default.
    On first use it becomes an array, (one element per cell in the range). You then try to compare it to a single value.
    I don't know if you need an array, because I don't know what you are trying to achieve.

    If Cells(i, 1).Value <> variable or (variable + .1 ) Then

    taking variable + .1 to mean aanother variable (variable1)

    then even

    If Cells(i, 1).Value <> variable or variable1 Then

    would probably not be what you want since or is a bitwise operation.

    If (Cells(i, 1).Value <> variable) or (Cells(i, 1).Value <> variable1) Then
    would be better.

    A tidier way would be

    Select case Cells(i,1).Value
       Case variable, variable1
           'do nothing
       Case Else
           'it's none of them, so action
    End Select


    Author Comment

    Thanks GrahamSkan; I am at home right now without access to the file, but I will test the "Case" code you have posted tomorrow and report back to you.

    As far as the array is concerned I believe I do need one because if the cell value matches anything in the A2:A22 range I would like those cells to be shown.  Can you help me with that piece assuming the need for an array?

    Thank you!
    LVL 76

    Accepted Solution

    Hi Scott
    I presume that you only want to show the rows in MySheet2 where their first cell value is matched by by one in MySheet.

    You will have to walk through the cells in each sheet with a one to one comparison. Note that there is no need to activate sheets to address them. This reduces the need to manipulate the ScreenUpdating property.

    Sub btn1_Click()
        Dim i As Long
        Dim j As Integer
        Dim bHide As Boolean
        For i = 28 To Sheets("Mysheet2").UsedRange.Rows.Count
            bHide = True
            For j = 2 To 22 'Given range
                If Sheets("MySheet2").Cells(i, 1).Value = Sheets("MySheet").Cells(j, 1) Then
                    bHide = False
                    Exit For 'Decision made. No need to look at the rest
                End If
            Next j
            Sheets("MySheet2").Rows(i).Hidden = bHide
        Next i
    End Sub


    Author Comment

    Awesome, thanks GrahamSkan- I was able to use the select case and your latest post as solutions.

    I have an additional request that I would like incorporated into this code if possible.  I am willing to post additional points for you if you are able to help me out.

    Is it possible to have all of the rows (from columns B:R only) that match your last btn1_Click post become bold and have a thick top border and thin lower border (just horizontal, no vertical borders) to avoid manually formatting these rows for presentation purposes (it would also be useful to "debold" and remove all horizontal borders from the other rows)?

    Thank you,
    LVL 76

    Expert Comment

    Hi Scott
    I'm not an Excel specialist and I don't currently know how to do that.

    It might pay you to record a macro while doing it manually. You can then examine the code that is produced.

    If you still can't work it out, I suggest that you post a question in the Excel Area

    All the best, Graham

    Author Comment

    Thanks Graham I'll check it out and probably end up posting a question!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
    You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now