I'm trying to teach myself VBA and really struggling I've managed to write some code that hides columns based on specific criteria and it is assigned to a button. I don't think it is very efficient though. In short, I have 26 accounts each represented by two columns, one for the name and the column to the right of the name shows the balance of that account. I won't always have all 26 accounts and so I want to hide those columns if there is nothing in the name cell. What I've done is put IF statements in each name and balance cell looking for a name in the previous name cell. If there is nothing entered in the previous name cell the IF enters an "*" in the current cell. I.E. in E10 I'd have, =IF(C10="","*",""), because C10 is the previous account name cell that may have an account name . The macro then looks for cells with an "*", on the row of names and hides the column if an "*" is present. The code I've used is:
------------------------------------------------------------
Sub HideCols()
Sheets("Budgeted").Unprotect
Dim rng As Range
Set rng = Range("Budgeted!e7:co7")
For Each cell In rng
If InStr(cell, "*") Then
cell.EntireColumn.Hidden = True
Else
cell.EntireColumn.Hidden = False
End If
Next
Sheets("Budgeted").Protect
End Sub
-----------------------------------------------------------
I have to click the button the macro is assigned to to run it, but I'd like it to run automatically so if I enter an account name the next set of name/balance columns are unhidden. In addition, there must be a way to simply look for the fact that something is entered in the previous name cell to prompt hidding or unhidding columns rather than my method of IFs and "*".
Any ideas? If I haven't provided enough information, let me know and I'll to provide it. Thanks.
Bookmarks