VBA Hiding Columns based on Multiple Conditions

[cwest66]

Hi,

I've got a quick question here.

I have some data that looks like this

%Pct Tag Tag Tag Note
.5 SS
.02 CS REVIEW
.03 BY PARTIAL
.9
.01 SL

I'm looking to hide any column that doesn't have any cells that fit the following logic based on this logic:

if {row contains (SL or CS or SS or BY) AND abs(%Pct)>.05} or {row contains row contains (SL or CS or SS or BY) AND Note is (REVIEW or PARTIAL or REJECT)}

In this case, the 3rd column would get hidden because the only cell containing a value does not meet the parameters.

my current VBA looks like this, but I am getting an error on the IF statement:

Sub HideRows()


Dim col As Integer
Dim row As Integer
Dim i As Integer
Dim MyRange As Variant
Dim Pct As Variant
Dim Note As Variant
Dim x As Integer
Dim y As Integer


col = ActiveSheet.UsedRange.Columns.Count - 5
row = ActiveSheet.Range("A" & Rows.Count).End(xlUp).row
Set Pct = ActiveSheet.Range(Cells(2, 2), Cells(2, col))
Set Note = ActiveSheet.Range(Cells(2, col + 3), Cells(row, col + 3))


For i = 3 To 3
Set MyRange = ActiveSheet.Range(Cells(2, i), Cells(row, i))

For x = 1 To col
If MyRange(x) = (("SL" Or "CS" Or "SS" Or "BY") And Abs(Pct(x) > 0.05)) Or (MyRange(x) = ("SL" Or "CS" Or "SS" Or "BY") And Note(x) = ("PARTIAL" Or "REVIEW" Or "REJECT")) Then
y = y + 1
End If

Next x

If y = 0 Then
Columns(i).Hidden = True

Next i




End Sub

 

[szymagic]

This may seem a little wild, but have you thought about breaking everything up like this:

If MyRange(x) = "SL" AND Abs(Pct(x) > 0.05 OR MyRange(x) = "CS" AND Abs(Pct(x) > 0.05 OR MyRange(x) = "SS" AND Abs(Pct(x) > 0.05 OR MyRange(x) = "BY" AND Abs(Pct(x) > 0.05 OR MyRange(x) = "SL" AND Note(x) = "PARTIAL" OR MyRange(x) = "SL" AND Note(x) = "REVIEW" OR MyRange(x) = "CS" AND Note(x) = "PARTIAL" OR MyRange(x) = "CS" AND Note(x) = "REVIEW" OR MyRange(x) = "CS" AND Note(x) = "REJECT" OR MyRange(x) = "SS" AND Note(x) = "PARTIAL" OR MyRange(x) = "SS" AND Note(x) = "REVIEW" OR MyRange(x) = "SS" AND Note(x) = "REJECT" OR MyRange(x) = "BY" AND Note(x) = "PARTIAL" OR MyRange(x) = "BY" AND Note(x) = "REVIEW" OR MyRange(x) ="BY" AND Note(x) = "REJECT" Then


I have no idea if this will work or not ha! But I guess it's worth a try!

Sorry if this ends up not working!

 

[cwest66]

This may seem a little wild, but have you thought about breaking everything up like this:

If MyRange(x) = "SL" AND Abs(Pct(x) > 0.05 OR MyRange(x) = "CS" AND Abs(Pct(x) > 0.05 OR MyRange(x) = "SS" AND Abs(Pct(x) > 0.05 OR MyRange(x) = "BY" AND Abs(Pct(x) > 0.05 OR MyRange(x) = "SL" AND Note(x) = "PARTIAL" OR MyRange(x) = "SL" AND Note(x) = "REVIEW" OR MyRange(x) = "CS" AND Note(x) = "PARTIAL" OR MyRange(x) = "CS" AND Note(x) = "REVIEW" OR MyRange(x) = "CS" AND Note(x) = "REJECT" OR MyRange(x) = "SS" AND Note(x) = "PARTIAL" OR MyRange(x) = "SS" AND Note(x) = "REVIEW" OR MyRange(x) = "SS" AND Note(x) = "REJECT" OR MyRange(x) = "BY" AND Note(x) = "PARTIAL" OR MyRange(x) = "BY" AND Note(x) = "REVIEW" OR MyRange(x) ="BY" AND Note(x) = "REJECT" Then


I have no idea if this will work or not ha! But I guess it's worth a try!

Sorry if this ends up not working!

Unfortunately, this gave me an error right too

 

[Joe4]

You have a bigger problem... the way you are trying to cycle through your ranges.
This is an invalid range reference:
MyRange(x)

If you want to loop through all the cells in your MyRange range, you can do so like this:

For each cell in MyRange
    If cell.Value="SL" Then
        ...
    End If
    ....
Next cell

 

[cwest66]

You have a bigger problem... the way you are trying to cycle through your ranges.
This is an invalid range reference:
MyRange(x)

If you want to loop through all the cells in your MyRange range, you can do so like this:

For each cell in MyRange
    If cell.Value="SL" Then
        ...
    End If
    ....
Next cell

I to that method of switching through cells, but am still getting a run-time error:

Sub HideRows()


Dim col As Integer
Dim row As Integer
Dim i As Integer
Dim MyRange As Variant
Dim ReturnsRange As Variant
Dim StockLoan As Variant
Dim x As Integer
Dim y As Integer


col = ActiveSheet.UsedRange.Columns.Count - 5
row = ActiveSheet.Range("A" & Rows.Count).End(xlUp).row
Set ReturnsRange = ActiveSheet.Range(Cells(2, 2), Cells(2, col))
Set StockLoan = ActiveSheet.Range(Cells(2, col + 3), Cells(row, col + 3))


For i = 3 To 3
    Set MyRange = ActiveSheet.Range(Cells(2, i), Cells(row, i))
        
    For Each cell In MyRange
        If cell.Value = (("SL" Or "CS" Or "SS" Or "BY") And Abs(ReturnsRange(x) > 0.05)) Or (MyRange(x) = ("SL" Or "CS" Or "SS" Or "BY") And StockLoan(x) = ("PARTIAL" Or "REVIEW" Or "REJECT")) Then
            y = y + 1
        End If
        
    Next cell
        
    If y = 0 Then
    Columns(i).Hidden = True
    End If
Next i




End Sub

 

[Joe4]

You are using that same flawed functionality in your formula with MyRange(x), ReturnsRange(x), and StockLoan(x).