VBA to unhide rows based on matching cell contents

RosieRose

Board Regular
Joined
Jul 29, 2014
Messages
75
I have worktables that I have formatted to hide rows based on data found in the table. Some of the rows have parts A,B,C and D. for instance perhaps I have 100 rows, but only 25 individual items as they all have parts A,B,C, and D. Is there a way to match the case based on the last letter of the cell contents?
Code:
Sub report2()
Dim i As Integer
Dim j As Integer
    For i = 7 To 69
    
          
        If Cells(i, 5) > -1 And Cells(i, 5) < 1 And Cells(i, 8) > -1 And Cells(i, 8) < 1 And Cells(i, 11) > -1 And Cells(i, 11) < 1 And Cells(i, 14) > -1 And Cells(i, 14) < 1 Then
                Cells(i, 5).EntireRow.Hidden = True
     
         ElseIf Cells(i, 5) = "n/a" Then
         Cells(i, 5).EntireRow.Hidden = True
         ElseIf Cells(i, 8) = "n/a" Then
         Cells(i, 8).EntireRow.Hidden = True
         ElseIf Cells(i, 11) = "n/a" Then
         Cells(i, 11).EntireRow.Hidden = True
         ElseIf Cells(i, 14) = "n/a" Then
         Cells(i, 14).EntireRow.Hidden = True
        
        For j = 1 To 69
            With Cells(j, 1).Font
                If .Strikethrough = True Then
                Cells(j, 1).EntireRow.Hidden = True
        End If
            End With
Next
      
          End If
 
    Next i

End Sub
is there a way to modify this so that if 10 B is not hidden, to also make sure that 10 A, C and D are not hidden either? Thank you
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

WarPigl3t

Well-known Member
Joined
May 25, 2014
Messages
1,609
First I'd like to talk about your first IF statement. It would be simplier if you just wrote
Code:
If Cells(i, 5) = 0 And Cells(i, 8) = 0 And Cells(i, 11) = 0 And Cells(i, 13) = 0 Then
Because if it is greater than -1 and less then 1 then it has to be 0.

Secondly, you might as well combine the ElseIf statements with the first If Statment
Code:
If (Cells(i, 5) = 0 And Cells(i, 8) = 0 And Cells(i, 11) = 0 And Cells(i, 13) = 0) OR _
Cells(i, 5) = "n/a" OR Cells(i, 8) = "n/a" OR Cells(i, 11) = "n/a" OR Cells(i, 13) = "n/a" Then
Rows(i).Hidden = TRUE
Else
Rows(i).Hidden = False
Thirdly, why is the End If inside of the End With? Makes no sense.

As for your primary question, I am not sure what you are asking. You can check what the last character in a cell is like this
myExample = RIGHT(Cells(i, 5), 1)
Hope this helps
 

RosieRose

Board Regular
Joined
Jul 29, 2014
Messages
75
Thank you warpiglet. I am pretty new to this, thus you are right I am not sure why my End if is inside the Endwith. As far as making the cells =0, there are thousands of number possibilities that I have between -1 and 1. Perhaps BVA treats all numbers as integers? my data has up to 8 decimal places (only displays 2), therefor most of the data I am looking at is between -1 and 1 (e.g 0.5924 or -0.9823)
 

RosieRose

Board Regular
Joined
Jul 29, 2014
Messages
75
Hi again, I tried the example of making the cells =0. This does not work for me as pretty much none of the cells =0, as I mentioned they are mostly in between -1 and 1
 

RosieRose

Board Regular
Joined
Jul 29, 2014
Messages
75
Sorry for the multiple replies. To clarify on my main question: lets say in column one I have the following item #'s: 100A, 100B, 100c, and 100D. based on the values in cell (i, 5) only 100B has a value greater than 1 or less than -1. so now, with my previous code 100A, 100C, and 100D are all hidden. Because they are all related to 100B (item number) I want all these items to show regardless that my previous code would indicate that they should be hidden. make sense?
 

Watch MrExcel Video

Forum statistics

Threads
1,109,001
Messages
5,526,203
Members
409,686
Latest member
Tori83

This Week's Hot Topics

Top