Hi everyone,
I just took ownership of someone else's Excel file, and some of the code seems to be broken. I'm very far from an excel expert - so would really appreciate any help!!
When I edit cell C18 in the attached picture the code should run, and add or take away sections depending on how many "channels" I need - but it breaks every time:
Below is the code that is messing things up - the bug seems to be in the third line of the "INIT" section.
Very happy to send the file to anyone that thinks they could help. Thank you so much!!
Public Sub HideChannels(ByVal iChannels As Integer)
Dim xlSheet As Excel.Worksheet
Dim xlRange As Excel.Range
Dim xlFormulaRange As Excel.Range
Dim mFormula As New Scripting.Dictionary
Dim lHSRow As Long
Dim lHERow As Long
Set xlSheet = ThisWorkbook.Worksheets(SHEETNAME_SUMMARY)
' GET ROW
Set xlRange = xlSheet.Range(DECISION_COLUMN)
For Each xlFormulaRange In xlRange.SpecialCells(xlCellTypeFormulas)
mFormula.Add Key:=xlFormulaRange.Formula, Item:=xlFormulaRange.Row
Next xlFormulaRange
Set xlFormulaRange = Nothing
Set xlRange = Nothing
' INIT
lHSRow = mFormula(Replace(USER_FORMULA_1, "%rownumber%", "2")) - 1
lHERow = mFormula(Replace(USER_FORMULA_2, "%rownumber%", CStr(LAST_CHANNEL_NUM))) + 1
xlSheet.Range(CStr(lHSRow) & ":" & CStr(lHERow - 1)).EntireRow.Hidden = False
' Channels=4:Exit
If iChannels = 4 Then
GoTo ExitSub:
End If
' HIDDEN.1-CHANNEL1...CHANNEL4...
With xlSheet
lHSRow = mFormula(Replace(USER_FORMULA_1, "%rownumber%", CStr(iChannels + 1))) - 1
lHERow = .Range(DECISION_COLUMN).Find(What:=DELIMITER_STRING).Row - 1
.Range(CStr(lHSRow) & ":" & CStr(lHERow)).EntireRow.Hidden = True
End With
' HIDDEN.2-TABLE:CHANNEL1...CHANNEL4...
With xlSheet
lHSRow = mFormula(Replace(USER_FORMULA_2, "%rownumber%", CStr(iChannels + 1)))
lHERow = mFormula(Replace(USER_FORMULA_2, "%rownumber%", CStr(LAST_CHANNEL_NUM)))
.Range(CStr(lHSRow) & ":" & CStr(lHERow)).EntireRow.Hidden = True
End With
ExitSub:
Set mFormula = Nothing
Set xlFormulaRange = Nothing
Set xlRange = Nothing
Set xlSheet = Nothing
End Sub
Bookmarks