Shortening VBA code (Show/Hide columns)

[Salcybercat]

Hey guys, need help on a very length code I'm using Excel 2007.

The way I programmed my Excel spreadsheet is: specific columns in a different worksheet ('Table') are hidden/shown according to answers in cells in worksheet 'Questions'.

The code I included below is just a snippet of the whole Worksheet_Calculate(). It goes all the way to column FQ, so you can imagine how slow the program runs

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
If Range("E32").Value = 0 Then
Sheets("Table").Columns("Z").EntireColumn.Hidden = True
Else
Sheets("Table").Columns("Z").EntireColumn.Hidden = False
End If
If Range("F32").Value = 0 Then
Sheets("Table").Columns("AA").EntireColumn.Hidden = True
Else
Sheets("Table").Columns("AA").EntireColumn.Hidden = False
End If
If Range("G32").Value = 0 Then
Sheets("Table").Columns("AB").EntireColumn.Hidden = True
Else
Sheets("Table").Columns("AB").EntireColumn.Hidden = False
End If

Any suggestions on how to shorten the process?? Help is much appreciated!

 

[circledchicken]

Hi,

This may help. I have added lots of comments in the code to hopefully clarify the process used. I learnt this kind of loop structure from this very nice article on the efficient use of loops - http://www.ozgrid.com/VBA/VBALoops.htm

Private Sub Worksheet_Calculate()
 
' define variables to be used later
Dim iLoop As Integer
Dim myRng As Range
Dim c As Range
Dim i As Long
 
    ' Start by unhiding everything on the Table sheet.
    ' Then we only need to find zero's and hide them
    ' (E on the answer sheet maps to Z on the Question sheet
    '  F maps to AA etc, i.e there's a 21 column difference)
    Sheets("Table").Range("E32:EV32").Offset(0, 21).EntireColumn.Hidden = False
    ' The above is equivalent to - Sheets("Table").Range("Z32:FQ32").EntireColumn.Hidden = False
 
    Set myRng = Sheets("Questions").Range("E32:I32") ' set variable myRng to the range with zero's on the Question sheet
    iLoop = WorksheetFunction.CountIf(myRng, 0) ' count the number of zero's in the range and store in variable iLoop
 
    Set c = myRng.Cells(myRng.Rows.Count, myRng.Columns.Count) ' set variable c to the last cell
                                                               ' in the range with zero's (can really set it
                                                               ' to any part in that range but setting it to the last
                                                               ' means the first cell searched will be the first cell in the range)
 
    ' loop through the zero's and hide the associated rows in the Answer sheet
    For i = 1 To iLoop
        ' c returns the next zero cell
        Set c = myRng.Find(What:=0, After:=c, LookIn:=xlValues, _
                LookAt:=xlWhole, SearchOrder:=xlByColumns, _
                SearchDirection:=xlNext, MatchCase:=False)
 
        ' Use the c returned above and the 21 column difference
        ' to hide the appropriate columns
        Sheets("Table").Columns(c.Column + 21).EntireColumn.Hidden = True
    Next i
End Sub

Also, do you need to have this within the Worksheet_Calculate event (which fires every time the worksheet calculates as 'HalfAce' pointed out in a previous post of mine). Would it be sufficient to assign the code to a button and then click/run it only when you need to?

You may also want to use a few other generic methods to speed up your code (e.g. turn off screen updating). Here is another good article on ways to do that:
http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm

 

[Salcybercat]

Hi circledchicken,
Thanks for the code. I'm trying to tailor it according to my worksheets. Unfortunately, I get an Error 91 for this line:

Sheets("Table").Columns(c.Column + 21).EntireColumn.Hidden = True

ERROR 91: Object variable or With block variable not set

I think it's to do with 'c.Column', although I'm not sure as how to set it. Do you (or anyone else) know how to?

 

[circledchicken]

Hi,

Would you be able to post an example/describe how your spreadsheet is set up and what bits of the code you changed please?

 

[vaskov17]

Looks like the object "c" is not set, which means that myRng.Find didn't find anything. You should set up a check such as:

if not c is nothing then

to make sure c is set to something

 

[Rick Rothstein]

Hey guys, need help on a very length code I'm using Excel 2007.

The way I programmed my Excel spreadsheet is: specific columns in a different worksheet ('Table') are hidden/shown according to answers in cells in worksheet 'Questions'.

The code I included below is just a snippet of the whole Worksheet_Calculate(). It goes all the way to column FQ, so you can imagine how slow the program runs

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
If Range("E32").Value = 0 Then
Sheets("Table").Columns("Z").EntireColumn.Hidden = True
Else
Sheets("Table").Columns("Z").EntireColumn.Hidden = False
End If
If Range("F32").Value = 0 Then
Sheets("Table").Columns("AA").EntireColumn.Hidden = True
Else
Sheets("Table").Columns("AA").EntireColumn.Hidden = False
End If
If Range("G32").Value = 0 Then
Sheets("Table").Columns("AB").EntireColumn.Hidden = True
Else
Sheets("Table").Columns("AB").EntireColumn.Hidden = False
End If

Any suggestions on how to shorten the process?? Help is much appreciated!

I'm not sure whether this will be noticeably quicker, but it is definitely shorter than what you are currently using. Replace the code above (plus the rest of the code that you did not show us) with this...

Private Sub Worksheet_Calculate()
  Dim X As Long
  Application.ScreenUpdating = False
  For X = 0 To 147
    Sheets("Table").Columns("Z").Offset(, X).EntireColumn.Hidden = Range("E32").Offset(, X).Value = 0
  Next
  Application.ScreenUpdating = True
End Sub

 

[Salcybercat]

Hi Circledchicken,

 Private Sub Worksheet_Calculate()
 
Dim iLoop As Integer
Dim myRng As Range
Dim c As Range
Dim i As Long
 
    Sheets("Table").Range("E32:AE32").Offset(0, 21).EntireColumn.Hidden = False
 
    Set myRng = Sheets("Table").Range("E32:J32")
    iLoop = WorksheetFunction.CountIf(myRng, 0)
 
    Set c = myRng.Cells(myRng.Rows.Count, myRng.Columns.Count)
    For i = 1 To iLoop
        Set c = myRng.Find(What:=0, After:=c, LookIn:=xlValues, _
                LookAt:=xlWhole, SearchOrder:=xlByColumns, _
                SearchDirection:=xlNext, MatchCase:=False)
 
        Set c.Columns = Range("E32:AE32")
 
        Sheets("Table").Columns(c.Columns + 21).EntireColumn.Hidden = True
 
    Next i
End Sub

I've changed the code so that: specific columns in worksheet "Table" will be hidden/un-hidden according to cells in the same worksheet.

If cell E32 = 0, Column Z will be hidden.
If cell F32 = 0, Column AA will be hidden...

Hi Rick Rothstein,vaskov17,
Thanks, I'll try your suggestions and get back to you guys soon.

 

[Salcybercat]

Hi Rick Rothstein,
I've tried the code, changing X= 0 To 7 just for trial. The screen would flicker 7 times. I know Application.ScreenUpdating should fix this problem, but it's still happening, I wonder why.