• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Worksheet_Change(By Val Target) Limits - Improve VBA Efficiency

S

Sky188

New Member
Hello,

I'm trying to build a spreadsheet that has static cells on rows 3 - 2,862, the vast majority of which are initially hidden. As values are added to column C on the rows that aren't hidden, additional rows will unhide. For example, row 14 is shown while rows 15 - 62 are hidden. If C14 has a value, row 15 automatically unhides. If C15 has a value, row 16 automatically unhides, so on and so forth. I do this same type of function with differing patterns hundreds of times and it results in ~200,000 lines of VBA code, but the Sub I'm using maxes out around 60,000. Is there a more efficient way to do this same function? I've included my current code below, and attached a dummy spreadsheet.

Code: 
If Target.Address(False, False) = "C14" Then
    Select Case Target.Value
        Case "": Rows("15:15").Hidden = True
        Case Is <> "": Rows("15:15").Hidden = False
        Range("C15").Select
    End Select
    End If
    If Target.Address(False, False) = "C15" Then
    Select Case Target.Value
        Case "": Rows("16:16").Hidden = True
        Case Is <> "": Rows("16:16").Hidden = False
        Range("C16").Select
    End Select
    End If
    If Target.Address(False, False) = "C16" Then
    Select Case Target.Value
        Case "": Rows("17:17").Hidden = True
        Case Is <> "": Rows("17:17").Hidden = False
        Range("C17").Select
    End Select
    End If
 

Attachments

  • Chandoo Dummy File.xlsm
    93.5 KB · Views: 2
Sky188
Interesting ... for me
You write something and with next sentence You write something else ...
... and Your code is something else ... hmm?
Do You write always to same cell that C and row number? ... why C, if always as row?
If C13 then which rows should be visible with Your real file?
 
Good afternoon vletm,
All of my values are in column C but the row number changes. Column B always has a question while Column C is asking for an answer to it. My actual file matches my original post. If C13 has a value, unhide row 14. If C14 has a value, unhide row 15, etc.
 
How about
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column = 3 Then
      Target.Offset(1).EntireRow.Hidden = Target = ""
   End If
End Sub
 
You must log in or register to reply here.
Back
Top