Slow VBA - Need help finding bottleneck

[ppittman]

Hello,

This is my first time trying to tackle things in excel using VBA, so some of the items were shamelessly stolen off of various message boards and youtube tutorials and modified to fit my needs. However, this seems to be unreliable and very slow. It takes about 20 seconds to update and doesn't work every time. My goal was to hide rows that were 0's across the board and not blank (12 columns contain data). Any ideas? Thanks for the help!

Private Sub Worksheet_Change(ByVal Target As Range)'HideRows()


Sheet2.Unprotect Password:="----"




Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


Rows("5:900").Hidden = False
 
For Each c In Range("b6:b800,c6:c800,d6:d800,e6:e800,f6:f800,g6:g800,h6:h800,i6:i800,j6:j800,k6:k800,l6:l800,m6:m800,n6:n800")
 
  
If c.Value = 0 And c.Value <> "" _
And c.Offset(0, 1).Value = 0 And c.Offset(0, 1).Value <> "" _
And c.Offset(0, 2).Value = 0 And c.Offset(0, 2).Value <> "" _
And c.Offset(0, 3).Value = 0 And c.Offset(0, 3).Value <> "" _
And c.Offset(0, 4).Value = 0 And c.Offset(0, 4).Value <> "" _
And c.Offset(0, 5).Value = 0 And c.Offset(0, 5).Value <> "" _
And c.Offset(0, 6).Value = 0 And c.Offset(0, 6).Value <> "" _
And c.Offset(0, 7).Value = 0 And c.Offset(0, 7).Value <> "" _
And c.Offset(0, 8).Value = 0 And c.Offset(0, 8).Value <> "" _
And c.Offset(0, 9).Value = 0 And c.Offset(0, 9).Value <> "" _
And c.Offset(0, 10).Value = 0 And c.Offset(0, 10).Value <> "" _
And c.Offset(0, 11).Value = 0 And c.Offset(0, 11).Value <> "" _
And c.Offset(0, 12).Value = 0 And c.Offset(0, 12).Value <> "" _
Then Rows(c.Row).Hidden = True
Next




Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic


Sheet2.Protect Password:="----"


End Sub

 

[Norie]

That code is running on 10335 cells every time you change any cell on the worksheet.

Are you sure that's what you want to do?

Why not check the cell(s) that have actually been changed, i.e. Target?

 

[ppittman]

My apologies, but can you walk me through what you are suggesting. I am having trouble with new terminology associated with my VBA experiment.

 

[offthelip]

Using variant arrays to do the test will be much mcuh faster, this code takes a few milliseconds on my machine:

Sub test()

' load worksheet into a variant array
inarr = Range("b1:n800")
 For i = 6 To 80
  blank = True
  For j = 1 To 13
     If (inarr(i, j) <> "") Then
       blank = False
       Exit For
     End If
   Next j
 If blank Then
      Rows(i).Hidden = True
 End If
 Next i
End Sub

 

[Joe4]

Your best bet is to tell us, in plain English, how your data is structured, what is being changed (and how), and what exactly you want the code to do.

What Norie was referring to, is that your code checks EVERY single cell anytime a change is made to any cell. The "Target" cell is the cell that was just changed. So you have the ability to only check the cell that was changed, and not every single cell every single time. That is INFINITELY faster. Most of the time, it is not necessary to check the cells that aren't changing.

 

[ppittman]

Thanks Joe4, offthelip and Norie. I have a drop-down list in Cell B2, and when this workbook is made available for consumption that will be the only change allowed. When a new item is selected from the drop-down list the values in the worksheet are updated based on a few criteria using the formula below (also kind of slow but manageable)

=IF($B$2="Consolidated",SUMIFS('INCOME DATA'!$E:$E,'INCOME DATA'!$D:$D,'13 Month Comp'!B$5,'INCOME DATA'!$C:$C,'13 Month Comp'!$A9),SUMIFS('INCOME DATA'!$E:$E,'INCOME DATA'!$C:$C,$A9,'INCOME DATA'!$D:$D,B$5,'INCOME DATA'!$B:$B,'13 Month Comp'!$B$2))

Ultimately, my goal is to create a 13 month income statement for review by co-workers. It is working now, but I would like it to be a little quicker. This might be the best it can be considering the formula is evaluating nearly 70k rows.

 

[ppittman]

Thank you all. After further review my issue is with the formula, which I can streamline myself. Thanks! I looks forward to contributing something to this forum going forward.