VBA to unhide rows based on matching cell contents

[RosieRose]

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?

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

 

[WarPigl3t]

First I'd like to talk about your first IF statement. It would be simplier if you just wrote

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

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]

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]

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]

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?