Basic Or Statement and Range Question (used with Excel)

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
Sheets("MySheet2").Activate
LastRow = ActiveSheet.UsedRange.Rows.Count

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

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
with:
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,
Scott
GobernasterAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GrahamSkanRetiredCommented:
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



0
GobernasterAuthor Commented:
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!
-Scott
0
GrahamSkanRetiredCommented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

GobernasterAuthor Commented:
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,
Scott
0
GrahamSkanRetiredCommented:
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
0
GobernasterAuthor Commented:
Thanks Graham I'll check it out and probably end up posting a question!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.