基于单元格值隐藏多行的代码 [英] Code to hide multiple rows based on cell value

问题描述

我正在尝试检查单元格值，基于此，应该隐藏5行.

I am trying to check the cell value and based on that, 5 rows should be hidden.

例如，如果 S4 单元格值是< = 0 ，则它应将行从 4隐藏到8 ，然后再次将其隐藏选中 S9 单元格值，如果它是< = 0 ，则它应隐藏从 9到13 的行，依此类推.

For example if the S4 cell value is <=0 then it should hide Rows from 4 to 8 and then again it should check S9 cell value will and if it is <=0 then it should hide rows from 9 to 13 and so on.

像这样，我有1000多行.有更好的方法来解决这个问题吗?请告诉我.

Like this I have more than 1000 rows. Is there a better way handle this? Please let me know.

推荐答案

有一种叫做"For循环"的东西，可以帮助您遍历大量行而不必写下每一行.在此处阅读有关它们的信息: https://www.excel-easy.com/vba/loop.html

There is something called a "For loop" that helps you go through a big number of rows without having to write down every single one of them. Read about them here: https://www.excel-easy.com/vba/loop.html

我会给你一个基本的例子:

I will give you a basic example:

Sub hideSomeRows()

Dim currentRow As Long
Dim lastRow As Long
lastRow = 1000 'or any other last row you want to check

For currentRow = 1 To lastRow Step 5
    If Range("S" & currentRow).Value <= 0 Then
        Rows(currentRow & ":" & currentRow + 4).EntireRow.Hidden = True
    End If
Next currentRow


End Sub

这将循环遍历第1行和第1000行之间的每第5行，并且如果被检查行的S列中的值为负或零，则将自身和接下来的4行隐藏起来.

This will loop through every 5th row between row 1 and row 1000 and hide itself and the next 4 rows if there is a negative value or zero in column S of the inspected row.

我使代码尽可能简单，但是在使用它之前，您应该真正考虑限定"Range".此处很好地讨论了为什么它很重要，并且怎么做.在此处中找到一些快速的代码片段，其中显示了如何定义工作簿和工作表在代码中作为变量工作.并且此处是有关处理工作表的详细信息:

I kept the code as simple as possible, but you should really consider qualifying "Range" before using it. Here is a very good discussion of why it is important and how to do it. Find some quick code snippets here showing how to define the workbook and the worksheet you are working on as variables in your code. And here is in-depth information about working with worksheets:

要点:做

Dim wb As Workbook
Set wb = ThisWorkbook

Dim ws As Worksheet
Set ws = wb.Sheets("!!YOURWORKSHEETNAME!!") 'replace !!YOURWORKSHEETNAME!! with the name of your worksheet

，然后编写 ws.Range ，而不只是 Range .行也一样.你为什么问?尝试执行宏之前，先查看另一个工作表，您将看到，如果没有其他说明，您的宏将始终将 Range 解释为最后一个活动工作表的范围！

and then write ws.Range instead of just Range. Same goes for Rows. Why you ask? Try looking at another worksheet before executing your macro and you will see, that your macro is going to always interpret Range as a range of the last active worksheet if not told otherwise!

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