vba 使用宏隐藏 Excel 中的单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16666225/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Hiding Cells in Excel with a macro
提问by AnnelizeStoltz
I am desperatly looking for help with my excel macro to hide rows if a certain criteria is met. I have tried to enter all the code previously provided in other question answers and just cannot get the damn thing to run.
如果满足特定条件，我正在拼命地寻求有关我的 excel 宏的帮助以隐藏行。我试图输入之前在其他问题答案中提供的所有代码，但无法运行该死的东西。
My code below runs wonderfully without the row hidden part
我下面的代码在没有行隐藏部分的情况下运行得非常好
I need the following to calc before the template is generated
在生成模板之前，我需要以下内容进行计算
If range F30 to J30 is blank then rows 29 to 30 must be hidden
If range F33 to J33 is blank then rows 32 to 33 must be hidden
If range F30 to J33 is blank then rows 28 to 35 must be hidden
Can you please assist me
你能帮我吗
Function RangeName(sName As String) As String
RangeName = Application.Substitute(sName, " ", "_")
End Function
Sub MergePrint()
Dim wsForm As Worksheet, wsData As Worksheet
Dim sRngName As String, r As Long, c As Integer
Set wsForm = Worksheets("Template")
Set wsData = Worksheets("DataSource")
With wsData.Cells(1, 1).CurrentRegion
For r = 2 To .Rows.Count
If Not wsData.Cells(r, 1).EntireRow.Hidden Then
For c = 1 To .Columns.Count
sRngName = wsData.Cells(1, c).Value
Range(RangeName(sRngName)).Value = wsData.Cells(r, c)
Next
wsForm.PrintOut
End If
Next
End With
End Sub
回答by Our Man in Bananas
you could use formulas to mark a row for hiding, then use SpecialCellsto return the rows that need to be hidden and set the hiddenproperty to true or false.
您可以使用公式来标记要隐藏的行，然后使用SpecialCells返回需要隐藏的行并将该hidden属性设置为 true 或 false。
in another column (for example, column N) add this formula:
在另一列（例如，N 列）中添加以下公式：
=IF(LEN(CONCATENATE(F:F,G:G,H:H,I:I,J:J))=0,NA(),"")
=IF(LEN(CONCATENATE(F:F,G:G,H:H,I:I,J:J))=0,NA(),"")
you can also put this formula in programmatically using VBA:Range("N28:N35").Formula = "=IF(LEN(CONCATENATE(F:F,G:G,H:H,I:I,J:J))=0,NA(),"""")"
您还可以使用 VBA 以编程方式输入此公式：Range("N28:N35").Formula = "=IF(LEN(CONCATENATE(F:F,G:G,H:H,I:I,J:J))=0,NA(),"""")"
this will check the cells, and return an #N/Aerror if they are all blank.
这将检查单元格，#N/A如果它们都是空白的，则返回错误。
Now you can use the SpecialCells function in VBA to select the rows to be hidden:
现在您可以使用 VBA 中的 SpecialCells 函数来选择要隐藏的行：
Cells.SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Hidden = True
Cells.SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Hidden = True
回答by sous2817
Here's another way. I'm sure there's a way to not use so many if statements...
这是另一种方式。我确定有一种方法可以不使用这么多 if 语句...
Sub Test()
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
With Sheet1
If Application.WorksheetFunction.CountA(.Range("F30:J30")) = 0 Then
.Range("F29:J30").EntireRow.Hidden = True
Else
.Range("F29:J30").EntireRow.Hidden = False
End If
If Application.WorksheetFunction.CountA(.Range("F33:J33")) = 0 Then
.Range("F32:J33").EntireRow.Hidden = True
Else
.Range("F32:J33").EntireRow.Hidden = False
End If
If Application.WorksheetFunction.CountA(.Range("F30:J33")) = 0 Then
.Range("F28:J35").EntireRow.Hidden = True
Else
.Range("F28:J35").EntireRow.Hidden = False
End If
End With
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub
