根据单元格中的单词在excel中隐藏行 [英] hiding rows in excel based on words in a cell

问题描述

我在VBA非常新，一直在寻找可以做我想做的VBA代码。我已经尝试了几个，并尝试适应他们，但我似乎不能让他们正确，所以以为我会尝试一些帮助！

我有6个项目有在它们旁边的单元格中是YES或NO，表示该人是否在该项目上工作。这是由另一张工作表上该项目旁边的人员名称决定的，所以公式生成，而不是下降或键入值。

该项目然后有几个

如果项目旁边有一个NO（在C6中），我希望该项目的相应行被隐藏（第13行：

我想为每个项目重复这个项目，

所以c7中没有一个隐藏31 ：47，
a不在C8隐藏49:65，
a不在C9隐藏67:83，
a不在C10隐藏85：101，
a不在C11隐藏103：118 ，

我不知道这是否可能，并且已经在圈子中发生，真的希望有人可以帮助：）

这是我尝试过的一个修改，但我确定我做错了，抱歉不要在这之前张贴这个。

  Private Sub Worksheet_Change（ByVal Target As Range）
 
如果Target.Address =$ C $ 6然后
如果Target.Value =否然后
行（13:29）.EntireRow.Hidden = True 
 Else 
行（13:29）.EntireRow.Hidden = False 
 
如果Target。地址=$ C $ 7然后
如果Target.Value =否然后
行（31:47）.EntireRow.Hidden = True 
 Else 
行（31:47） .EntireRow.Hidden = False 
如果
结束If 
 
 
如果Target.Address =$ C $ 8然后
如果Target.Value = NO然后
行（49:65）.EntireRow.Hidden = True 
 Else 
行（49:65）.EntireRow.Hidden = False 
结束如果
结束如果
 
如果Target.Address =$ C $ 9然后
如果Target.Value =否然后
行（67:83）.EntireRow.Hidden = True 
 Else 
行（67:83）.EntireRow.Hidden = False 
如果
结束如果
 
 
如果Target.Address =$ C $ 10然后
如果Target.Value = NO然后
行（85：101）.EntireRow.Hidden = True 
 Else 
行（85：101）.EntireRow.Hidden =假
 
如果
结束If 
 
 
 
如果Target.Address =$ C $ 11然后
如果目标.Value = NO然后
行（103：119）.EntireRow.Hidden = True 
 Else 
行（103：119）.EntireRow.Hidden = False 
 
 End If 
结束如果
 
 
结束子
  

解决方案

最短代码我可以想到：

  Private Sub Worksheet_Change（ByVal Target As Range）
 On Error Resume Next 
 Dim bHide As Boolean 
 bHide =（InStr（1，Target.Value，NO，vbTextCompare）> 0）
选择案例Target.Address 
案例$ C $ 6
行（13:29）。EntireRow.Hidden = bHide 
案例$ C $ 7
行（31:47）。EntireRow.Hidden = bHide 
案例$ C $ 8
行（49:65）EntireRow.Hidden = bHide 
案例$ C $ 9
行（67:83）。EntireRow.Hidden = bHide 
案例$ C $ 10
行（85：101）。EntireRow.Hidden = bHide 
案例$ C $ 11
行（103：119）。EntireRow.Hidden = bHide 
结束选择
 End Sub 
  

请测试。

I am VERY new at VBA and have been searching for VBA code that can do what I want. I have tried several and attempted to adapt them however I just can't seem to get them right so thought I would try for some help!

I have 6 projects that have either YES or NO in the cell next to them indicating if the person is working on that project. This is determined by the persons name next to that project on another sheet, so is formula produced and not a drop down or typed in value.

The project then has a few rows below corresponding to it.

If there is a NO next to a project (in C6), I want the corresponding rows for that project to be hidden (rows 13:29).

I want this to be repeated for each project,

so a no in c7 hides 31:47, a no in C8 hides 49:65, a no in C9 hides 67:83, a no in C10 hides 85:101, a no in C11 hides 103:118,

I don't know if this is possible and have been going around in circles, really hope that someone can help :)

this is one of the adaptions i have tried but i am sure i am doing something wrong, sorry for not posting this here before

Private Sub Worksheet_Change(ByVal Target As Range) 

If Target.Address = "$C$6" Then 
If Target.Value = NO Then 
Rows(13:29).EntireRow.Hidden = True 
Else 
Rows(13:29).EntireRow.Hidden = False 

If Target.Address = "$C$7" Then 
If Target.Value = NO Then 
Rows(31:47).EntireRow.Hidden = True 
Else 
Rows(31:47).EntireRow.Hidden = False 
End If 
End If 


If Target.Address = "$C$8" Then 
If Target.Value = NO Then 
Rows(49:65).EntireRow.Hidden = True 
Else 
Rows(49:65).EntireRow.Hidden = False 
End If 
End If 

If Target.Address = "$C$9" Then 
If Target.Value = NO Then 
Rows(67:83).EntireRow.Hidden = True 
Else 
Rows(67:83).EntireRow.Hidden = False 
End If 
End If 


If Target.Address = "$C$10" Then 
If Target.Value = NO Then 
Rows(85:101).EntireRow.Hidden = True 
Else 
Rows(85:101).EntireRow.Hidden = False 

End If 
End If 



If Target.Address = "$C$11" Then 
If Target.Value = NO Then 
Rows(103:119).EntireRow.Hidden = True 
Else 
Rows(103:119).EntireRow.Hidden = False 

End If 
End If 


End Sub 

解决方案

Shortest code I can think of:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    Dim bHide As Boolean
    bHide = (InStr(1, Target.Value, "NO", vbTextCompare) > 0)
    Select Case Target.Address
        Case "$C$6"
            Rows("13:29").EntireRow.Hidden = bHide
        Case "$C$7"
            Rows("31:47").EntireRow.Hidden = bHide
        Case "$C$8"
            Rows("49:65").EntireRow.Hidden = bHide
        Case "$C$9"
            Rows("67:83").EntireRow.Hidden = bHide
        Case "$C$10"
            Rows("85:101").EntireRow.Hidden = bHide
        Case "$C$11"
            Rows("103:119").EntireRow.Hidden = bHide
    End Select
End Sub

Please test.

这篇关于根据单元格中的单词在excel中隐藏行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！