DarkJester89
Board Regular
- Joined
- Nov 5, 2017
- Messages
- 109
- Office Version
- 2016
- Platform
- Windows
Hello! I inherited this code from the last position holder. It works (partly, it doesn't hide the last column) but I just wanted to see if someone smarter than me could do a review, this is the longest code I've seen and I don't know if it's efficient as it can be.
It was built to:
- hide/unhide row based if column A is empty
- hide/unhide column based if row 1 is empty
- hide/unhide column based if row 2 is empty
- autofit row
- autofit column (but it crunches words)
Anything is much appreciated, thank you.
It was built to:
- hide/unhide row based if column A is empty
- hide/unhide column based if row 1 is empty
- hide/unhide column based if row 2 is empty
- autofit row
- autofit column (but it crunches words)
Anything is much appreciated, thank you.
VBA Code:
Sub Hide_ManualTrapDoor()
Dim ws As Worksheet
Dim lr As Integer, lc As Integer
Dim ir As Integer, ic As Integer, er As Integer, ec As Integer
If MsgBox("Please wait while your Rate Matrix is being constructed.", vbOKCancel, "NGSP") = vbCancel Then
Exit Sub
End If
Set ws = ThisWorkbook.Sheets("BIBS & TOPIC ASSIGN")
Application.Calculation = xlCalculationManual
lr = ws.Range("A1048576").End(xlUp).Row
lc = ws.Range("XFD2").End(xlToLeft).Column
For ir = 3 To lr
Select Case ws.Range("A" & ir).Value
Case 0, vbNullString
For er = ir To lr
Select Case ws.Range("A" & er).Value
Case Is <> 0
ws.Rows(ir & ":" & er).EntireRow.Hidden = True
''GoTo x
Case Else
'do nothing
End Select
ir = ir + 1
Next er
Case Else
'do nothing
End Select
Next ir
X:
For ic = 2 To lc
Select Case ws.Cells(2, ic).Value
Case 0, vbNullString
For ec = ic To lc + 2
Select Case ws.Cells(2, ec).Value
Case 0, vbNullString
Select Case ec
Case lc
ws.Range(Cells(2, ic), Cells(2, ec)).EntireColumn.Hidden = True
GoTo z
Case Else
'do nothing
End Select
Case Else
ws.Range(Cells(2, ic), Cells(2, ec)).EntireColumn.Hidden = True
ic = ec
GoTo Y
End Select
Next ec
Case Else
'do nothing
End Select
Y:
Next ic
z:
Application.Calculation = xlCalculationAutomatic
End Sub