跳到主要內容

如何在Excel中根據單元格值隱藏行?

假設您具有一定範圍的數據,並且想基於列值隱藏行,例如,如果列的單元格值小於3000,則隱藏該單元格的行,如下面的屏幕快照所示。


在Excel中,可以使用“過濾器”功能根據單元格值過濾和隱藏行。

1.選擇要過濾的數據,然後單擊 數據 > 篩選。 看截圖:

doc根據值2隱藏行

2.然後單擊向下箭頭以顯示過濾器下拉列表,然後單擊 數字過濾器(或文本過濾器) > 比...更棒 (您可以從子菜單中選擇所需的其他條件)。 看截圖:

doc根據值3隱藏行

3.在彈出的對話框中,在大於大於旁邊的文本框中鍵入條件。 看截圖:

doc根據值4隱藏行

4。 點擊 OK。 現在,僅顯示大於3000的數據,並且隱藏了小於3000的行。

doc根據值5隱藏行


此外,如果您對VBA代碼感興趣,在這裡我可以介紹一個VBA代碼以根據單元格值隱藏行。

1。 按 Alt + F11鍵 啟用鍵 Microsoft Visual Basic for Applications 窗口。

2。 然後點擊 插入 > 模塊 打開一個新的 模塊 窗口,然後將下面的VBA代碼粘貼到該窗口中。

VBA:根據單元格值隱藏行。

Sub HideRow()
'Updateby20150618
Dim Rng As Range
Dim WorkRng As Range
Dim xNumber As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
xNumber = Application.InputBox("Number", xTitleId, "", Type:=1)
For Each Rng In WorkRng
    Rng.EntireRow.Hidden = Rng.Value < xNumber
Next
End Sub

3.然後按 F5 鍵來運行VBA,然後在彈出對話框中選擇要隱藏行的數據范圍(標題除外)。 看截圖:

doc根據值6隱藏行

4。 點擊 OK,然後在第二個對話框中輸入標準編號。 看截圖:

doc根據值7隱藏行

5。 點擊 OK。 現在,其數據小於3000的行被隱藏。

小提示: 如果要隱藏大於3000的行,只需更改 Rng.EntireRow.Hidden = Rng.Value <xNumberRng.EntireRow.Hidden = Rng.Value> xNumber,或者如果您要隱藏數據等於3000的行,請更改為 Rng.EntireRow.Hidden = Rng.Value = xNumber.


根據Excel中的條件快速選擇單元格

使用Kutools for Excel的 選擇特定的單元格,您可以一次根據一兩個條件選擇單元格。 
doc選擇特定的單元格
 
Kutools for Excel:擁有300多個便捷的Excel加載項,可以在30天內免費試用。

如果您既不想對VBA啟用過濾器功能,也不想對VBA啟用過濾器功能,那麼在這裡我為您介紹一個方便的工具- 選擇特定的單元格 of Excel的Kutools 要根據單元格值快速選擇整個行,則可以將其隱藏。

Excel的Kutools, 與以上 300 方便的功能,使您的工作更加輕鬆。 

免費安裝 Kutools for Excel,請執行以下操作:

小費。 如果您想免費試用“提取文字”功能, 請免費試用Kutools for Excel 首先,然後按照上述步驟進行操作。

1.選擇數據范圍,然後單擊 庫工具 > 選擇 > 選擇特定的單元格。 看截圖:

doc根據值14隱藏行

2。 在裡面 選擇特定的單元格 對話框,檢查 整行 選擇類型 部分,然後從中選擇所需的條件 特定類型 列表,然後在文本框中鍵入數字或文本。 看截圖:

doc根據值9隱藏行

3。 點擊 Ok > OK 關閉對話框。 現在,選擇了數據小於3000的行,您只需要右鍵單擊行標題以顯示上下文菜單,然後單擊“隱藏”。 看截圖:
doc根據值10隱藏行

現在,其數據小於3000的行被隱藏。

doc根據值11隱藏行

小提示:

1.如果要隱藏包含特定值的行,則可以選擇 包含 然後在文本框中輸入特定值。

doc根據值12隱藏行

2.如果要隱藏包含大於或小於值的行,則可以選擇 比...更棒小於,然後在兩個框中鍵入值,然後檢查 .

doc根據值13隱藏行

使用Kutools for Excel的 選擇特定的單元格 實用程序,您可以識別特定單元格的位置,根據單元格值選擇整行或整列,依此類推。 單擊此處了解有關此實用程序的更多信息。

Kutools for Excel:Excel 中必須具備的 300 多個函數, 從這裡免費試用30天。

最佳辦公生產力工具

🤖 Kutools 人工智慧助手:基於以下內容徹底改變數據分析: 智慧執行   |  生成代碼  |  建立自訂公式  |  分析數據並產生圖表  |  呼叫 Kutools 函數...
熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup    多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧    工作簿和工作表管理器   |  資源庫 (自動文字)   |  日期選擇器   |  合併工作表   |  加密/解密單元格    按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 (過濾粗體/斜體/刪除線...)...
前 15 個工具集12 文本 工具 (添加文本, 刪除字符,...)   |   50+ 圖表 類型 (甘特圖,...)   |   40+ 實用 公式 (根據生日計算年齡,...)   |   19 插入 工具 (插入二維碼, 從路徑插入圖片,...)   |   12 轉化 工具 (數字到單詞, 貨幣兌換,...)   |   7 合併與拆分 工具 (高級合併行, 分裂細胞,...)   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能,體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

產品描述


Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!
Comments (17)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Yes, "Entire Row" is selected.
This comment was minimized by the moderator on the site
All rows remain selected without using Kutools. As soon as I use Kutools and select header row, all other rows but the header row de-select.
This comment was minimized by the moderator on the site
Are you sure check the Entire row option in the dialog? If you check cell option, the selected cells will be unselected when you right click at one row.
https://www.extendoffice.com/images/stories/comments/sun-comment/doc-select-specific-cell-1.png
This comment was minimized by the moderator on the site
Hi - I'm using a newer Lenovo PC with Windows 10 Professional and Microsoft 365 Apps for business. Excel Version 2210 (Build 15726.20202 Click-to-Run).
This comment was minimized by the moderator on the site
I have tried the operation in the same version with you, there is no any problem. Could you try to manually select several rows (without using Kutools for Excel), then right click at one row header, see all rows are remained selecting?
This comment was minimized by the moderator on the site
I have done exactly what you've demonstrated. However, when I right-click on the row header, all other rows become de-selected and only that one row remains selected to be hidden or deleted. No matter what I do, the other rows do not stay selected when right-clicking on the header. I'm at a loss to know what to do now.
This comment was minimized by the moderator on the site
I am so sorry for that. Could you tell me what Excel version and system you use?
This comment was minimized by the moderator on the site
Hello,

That is exactly what I've been doing, however, it only hides the row on which I have clicked the row header. All other selected rows remain unhidden, and are deselected at the same time. I will try it again following your gif exactly. Thanks for your reply.
This comment was minimized by the moderator on the site
I'm using Kutools to select entire rows based on a value, and it does select all the rows with that value. However, when I right-click on the row header and click on "Hide" in the menu, only that row is hidden and the rest that were selected become de-selected. It appears I still have to hide each row one at a time. What am I doing wrong?
This comment was minimized by the moderator on the site
Hi, Rochelley, I think there may be some wrong when you right-click at the row header. After the rows which match your condition are selecetd after applying the feature, right click at one row header of them, note that other rows are kept selected, then choose hide from the context menu. Please see the gif:
https://www.extendoffice.com/images/stories/comments/sun-comment/doc-hide-selected-row.gif
This comment was minimized by the moderator on the site
Hello,

I have a drop down with multiple reponses. I am trying to hide certain rows dependant on the response, can someone please assist?

Dropdown options are "CDI", "AR", "Legal", "Multiple" and "Other".

If response is CDI then hide rows 42-100

If response is AR then hide rows 19-41 and rows 66-100

If response is Legal then hide rows 19-66 and rows 88-100

If response is multiple then don't hide anything

If response is Other then hide rows 19-88



Can someone please asssist?
This comment was minimized by the moderator on the site
Hi I need some help with my work sheet. I have to build a dynamic questionnaire and I need to have a code that allow me to hide/ Unhide automatically some rows base on a cell information. Example:

if c6 is "internally" then show me row 7 but hide 8 to 107
if c6 is "Externally" then hide all the rows from 7 to 107


if c7 is "yes" then show me row 8 but hide 9 to 107
if c7 is "No" then show me row 8 but hide 9 to 107
if c8 is "Critical" then show me row 9 but hide 10 to 107

if c8 is "Important" show me row 9 but hide 10 to 107
if c8 is "Ordinary" show me row 9 but hide 10 to 107
if c8 is "Other" then show me row 9 but hide 10 to 107

if c9 is "Critical" then show me row 10 but hide 11 to 107
if c9 is "Important" then show me row 10 but hide 11 to 107
if c9 is "Ordinary" then show me row 10 but hide 11 to 107
if c9 is "Other" then show me row 10 but hide 11 to 107

etc.

can somebody help me?
This comment was minimized by the moderator on the site
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Row = 1 Then

If Target.Value = "YES" Then
Worksheets(2).Activate
Worksheets(2).Application.Columns("A:Z").Select
Worksheets(2).Application.Selection.EntireColumn.Hidden = True
Worksheets(2).Application.Columns("AA:AZ").Select
Worksheets(2).Application.Selection.EntireColumn.Hidden = False
ElseIf Target.Value = "NO" Then
Worksheets(2).Activate
Worksheets(2).Application.Columns("AA:AZ").Select
Worksheets(2).Application.Selection.EntireColumn.Hidden = True
Worksheets(2).Application.Columns("A:Z").Select
Worksheets(2).Application.Selection.EntireColumn.Hidden = False
Else
Worksheets(2).Activate
Worksheets(2).Application.Columns("A:Z").Select
Worksheets(2).Application.Selection.EntireColumn.Hidden = False
Worksheets(2).Application.Columns("AA:AZ").Select
Worksheets(2).Application.Selection.EntireColumn.Hidden = False
End If

End If
End Sub
This comment was minimized by the moderator on the site
I got same error
This comment was minimized by the moderator on the site
Sorry I do not know either.
This comment was minimized by the moderator on the site
I don't know either Sunny
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations