Excel中基于单元格值的多个非连续行 [英] Multiple Non-contiguous rows in excel Based on cell value

问题描述

我的目标是能够创建一个下拉列表，该列表根据我创建的列表中个人的姓名隐藏excel中某些不连续的行.我有这段代码，是我在Youtube上找到的，并且想知道这是怎么回事，因为它不起作用.我对VBA比较陌生

My goal is to be able to have a drop down list that hides certain non-contiguous rows in excel based off the name of the individual in the list I create. I have this code which I found off Youtube and was wondering what was wrong with it as it was not working. I am relatively new to VBA

Private Sub Worksheet_Calculate()
Dim Andrew, Robert, Michael As Range
Set Andrew = Range("K30")
Set Robert = Range("K30")
Set Michael= Range ("K30")

Select Case Andrew
        Case Is = "Andrew": Rows("8:10").EntireRow.Hidden = True
                            Rows("11:12").EntireRow.Hidden = False
                            Rows("13:13").EntireRow.Hidden = True
                            Rows("14:25").EntireRow.Hidden = False

End Select

Select Case Robert
        Case Is = "Robert"
                            Rows("6:20").EntireRow.Hidden = True
                            Rows("21:25").EntireRow.Hidden = False


End Select

Select Case  Michael
        Case Is = "Michael"
                            Rows("1:5").EntireRow.Hidden = True
                            Rows("6:25").EntireRow.Hidden = False
End Select
End Sub

推荐答案

下拉工作表更改

• 当您通过"下拉列表"更改单元格中的值时，

  Drop Down Worksheet Change

  • When you change a value in a cell via 'drop down', the Worksheet.Change event is triggered.
  • Copy the first code into the appropriate sheet module e.g. Sheet1.
  • Copy the second code into a standard module, e.g. Module1.
  • You do not run anything, it is automatically showing or hiding rows.

  Sheet1

  Option Explicit
  
  Private Sub Worksheet_Change(ByVal Target As Range)
      Const RangeAddress As String = "K30"
      If Not Intersect(Target, Me.Range(RangeAddress)) Is Nothing Then
          manipulateRows Me, Target.Value
      End If
  End Sub
  

  Module1

  Option Explicit
  
  Sub manipulateRows(Sheet As Worksheet, checkString As String)
      With Sheet
          Select Case checkString
              Case "Andrew"
                  .Rows("8:10").Hidden = True
                  .Rows(13).Hidden = True
                  .Rows("11:12").Hidden = False
                  .Rows("14:25").Hidden = False
              Case "Michael"
                  .Rows("1:5").Hidden = True
                  .Rows("6:25").Hidden = False
              Case "Robert"
                  .Rows("6:20").Hidden = True
                  .Rows("21:25").Hidden = False
              Case Else ' When DEL is pressed (Empty Cell), shows all rows.
                  .Rows("1:25").Hidden = False
          End Select
      End With
  End Sub
  

  这篇关于Excel中基于单元格值的多个非连续行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！