• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 132
  • Last Modified:

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
0
Gobernaster
Asked:
Gobernaster
  • 3
  • 3
1 Solution
 
GrahamSkanCommented:
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
 
GrahamSkanCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now