So I have been charged with coming up with an Excel program that can hide rows assuming different conditions are met. To describe the type of data:
Column A, Row 2: Company Name
Column W, Row 3-7: Transaction Amount
Column X, Row 7: Sum of the transactions
I need to hide all companies whose transactions do not total 600 or more. If they do total 600 or more, I need all of the transactions to remain visible as well as the company name and the total. Very few companies share the same number of transactions so I originally set it up to check an empty cell in the company's name row. I don't have the code here since it is on my work computer (obviously, this is driving me insane right now). It was something like this:
Sub Totals()
lastrow = Cells(Rows.Count, "W").End(xlUp).Row
FirstRow = 2
TempTotal = 0
For x = FirstRow To lastrow + 1
If Cells(x, "W") <> "" Then
TempTotal = TempTotal + Cells(x, "W")
Else: Cells(x - 1, "X") = TempTotal
TempTotal = 0
End If
Next x
End Sub
I used this to sum the transactions. I was hoping to be able to use a nested If statement to print the transactions sum totals and, if they are less than 600, hide the relevant rows within the same code. I just have no idea how to specify the whole section... Any help would be appreciated!
The following code will specify the whole section and hide all relevant rows company name (Col A), transactions (Col W) and totals (Col X) for companies whose totals are less than 600. Companies whose totals are >= 600 will remain visible.
Option Explicit
Sub HideBelow600()
Dim LastRow As Long, topRow As Long, bottomRow As Long, i As Long
Dim ws As Worksheet: Set ws = ActiveSheet
LastRow = ws.Cells(Rows.Count, 24).End(xlUp).Row
For i = LastRow To 2 Step -1
If ws.Cells(i, 24).Value < 600 And ws.Cells(i, 24) <> "" Then
bottomRow = i
topRow = ws.Cells(i, 24).Offset(0, -1).End(xlUp).Row - 1
ws.Range(Sheet3.Cells(topRow, 24), ws.Cells(bottomRow, 24)).EntireRow.Hidden = True
End If
Next i
End Sub