隐藏/取消隐藏行-过程过长 [英] hide/unhide rows - procedure too long

问题描述

VBA的新增功能，并试图基于工作表1(F4)中单元格的值(值0-10)创建一个代码来隐藏/取消隐藏工作表2上的行(称为收费代码).我当前正在使用下面的代码，它可以通过单元格F23进行工作，但是此过程太长了.我需要为单元格F4:F53复制此代码，这些F单元中的每一个都有一组不同的10行对应的行来隐藏/取消隐藏在工作表2上.这是我用于F4的示例:

New to VBA and trying to create a code to hide/unhide rows on a sheet 2 (named Charge Codes) based on a value of a cell in sheet 1 (F4) (Values 0-10). I'm currently using the code below and it works through cell F23, but then the procedure is too long. I need to replicate this for cells F4:F53, with each of those F cells having a different corresponding set of 10 rows to hide/unhide on sheet 2. Can anyone help me compile this? This is the example I'm using for F4:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = ("$F$4") And Target.Value = "0" Then Sheets("Charge Codes").Rows("3:12").Hidden = True

If Target.Address = ("$F$4") And Target.Value = "1" Then Sheets("Charge Codes").Rows("3").EntireRow.Hidden = False: Sheets("Charge Codes").Rows("4:12").Hidden = True

If Target.Address = ("$F$4") And Target.Value = "2" Then Sheets("Charge Codes").Rows("3:4").EntireRow.Hidden = False: Sheets("Charge Codes").Rows("5:12").Hidden = True

If Target.Address = ("$F$4") And Target.Value = "3" Then Sheets("Charge Codes").Rows("3:5").EntireRow.Hidden = False: Sheets("Charge Codes").Rows("6:12").Hidden = True

If Target.Address = ("$F$4") And Target.Value = "4" Then Sheets("Charge Codes").Rows("3:6").EntireRow.Hidden = False: Sheets("Charge Codes").Rows("7:12").Hidden = True

If Target.Address = ("$F$4") And Target.Value = "5" Then Sheets("Charge Codes").Rows("3:7").EntireRow.Hidden = False: Sheets("Charge Codes").Rows("8:12").Hidden = True

If Target.Address = ("$F$4") And Target.Value = "6" Then Sheets("Charge Codes").Rows("3:8").EntireRow.Hidden = False: Sheets("Charge Codes").Rows("9:12").Hidden = True

If Target.Address = ("$F$4") And Target.Value = "7" Then Sheets("Charge Codes").Rows("3:9").EntireRow.Hidden = False: Sheets("Charge Codes").Rows("10:12").Hidden = True

If Target.Address = ("$F$4") And Target.Value = "8" Then Sheets("Charge Codes").Rows("3:10").EntireRow.Hidden = False: Sheets("Charge Codes").Rows("11:12").Hidden = True

If Target.Address = ("$F$4") And Target.Value = "9" Then Sheets("Charge Codes").Rows("3:11").EntireRow.Hidden = False: Sheets("Charge Codes").Rows("12:12").Hidden = True

If Target.Address = ("$F$4") And Target.Value = "10" Then Sheets("Charge Codes").Rows("3:12").EntireRow.Hidden = False

End Sub

推荐答案

此方法使用嵌套的Ifs:

This uses nested Ifs:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = ("$F$4") Then

    If Target.Value <> 0 Then
        Sheets("Charge Codes").Rows("3:12").Hidden = True
    ElseIf Target.Value = 10 Then
        Sheets("Charge Codes").Rows("3:12").Hidden = False
    Else
        Sheets("Charge Codes").Rows(3 + Target.Value & ":12").Hidden = True
        Sheets("Charge Codes").Rows("3:" & 2 + Target.Value).Hidden = False
    End If

End Sub

这篇关于隐藏/取消隐藏行-过程过长的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！