VBA 使用单元格引用隐藏列和行 [英] VBA Hiding Columns and Rows using Cell Reference

问题描述

我想根据对某些单元格的输入隐藏某些列和行，以使代码易于编辑.

此外，如果参考单元格/范围为空，那么我想继续代码而不隐藏任何行或列.

要隐藏的列:基于单元格 C8:D8

要隐藏的行:基于单元格 C9:D9

这是合乎逻辑的节日应该发生的地方

<预><代码>reportColumnsAddr = settingsSheet.Range("C8").Value &":" &settingsSheet.Range("D8").ValuereportRowsAddr = settingsSheet.Range("C9").Value &":" &settingsSheet.Range("D9").Value

当前代码

选项显式私有子 CommandButton1_Click()Dim MyFolder As String, MyFile As String将开始时间调暗为两倍Dim MinutesElapsed As String将文件名变暗为字符串将单元格调暗为字符串昏暗的计数器只要长If ThisWorkbook.Sheets("Sheet1").Range("C7").Value = vbNullString ThenMsgBox "输入标签名称"退出子万一使用 Application.FileDialog(msoFileDialogFolderPicker).AllowMultiSelect = False.Title = "选择文件夹"如果 .Show = True 那么我的文件夹 = .SelectedItems(1)万一如果 .SelectedItems.Count = 0 然后退出子错误清除结束于Application.ScreenUpdating = FalseApplication.DisplayStatusBar = FalseApplication.EnableEvents = FalseApplication.Calculation = xlCalculationManualMyFile = Dir(MyFolder & "\", vbReadOnly)开始时间 = 计时器执行 While MyFile <>"事件出错时转到 0Workbooks.Open Filename:=MyFolder &\" &我的文件，更新链接:=假Dim settingsSheet As Worksheet '源Dim reportSheet As Worksheet '要转换为 PDFDim targetColumnsRange As Range'来自源将 targetRowsRange 调暗为范围Dim reportSheetName As String '带有目标工作表名称的源工作表Dim reportColumnsAddr As StringDim reportRowsAddr 作为字符串' 设置对设置表的引用Set settingsSheet = ThisWorkbook.Worksheets("Sheet1") ' source' 收集报表名称reportSheetName = settingsSheet.Range("C7").Value '好reportColumnsAddr = settingsSheet.Range("C8").Value &":" &settingsSheet.Range("D8").ValuereportRowsAddr = settingsSheet.Range("C9").Value &":" &settingsSheet.Range("D9").Value设置 reportSheet = Sheets(reportSheetName)设置 targetColumnsRange = reportSheet.Range(reportColumnsAddr)设置 targetRowsRange = reportSheet.Range(reportRowsAddr)targetColumnsRange.EntireColumn.Hidden = TruetargetRowsRange.EntireRow.Hidden = True使用 reportSheet.PageSetup.Zoom = 错误.FitToPagesWide = 1 '.FitToPagesTall = 1结束于文件名 = ActiveWorkbook.Name单元格 = 替换(文件名，.xlsx"，.PDF")报告表.选择reportSheet.PageSetup.Orientation = xlLandscapeActiveSheet.ExportAsFixedFormat 类型:=xlTypePDF，文件名:= _ThisWorkbook.Path &\" &细胞， _质量:=xlQualityStandard，IncludeDocProperties:=True，_IgnorePrintAreas:=True, OpenAfterPublish:=False计数器 = 计数器 + 10工作簿(MyFile).关闭 SaveChanges:=False我的文件 = 目录环形'重新打开设置Application.ScreenUpdating = TrueApplication.DisplayStatusBar = TrueApplication.EnableEvents = TrueApplication.Calculation = xlCalculationManualMinutesElapsed = Format((Timer - StartTime)/86400, "hh:mm:ss")MsgBox "成功转换" &柜台&文件" &已用分钟数"分钟", vb信息结束子

解决方案

关于你的代码的一些想法:

<块引用>

将 MySheet 变暗为字符串

1. 由于 mySheet 指的是工作表名称，所以要清楚.

   将变量重命名为 Dim mySheetName as String

---

<块引用>

设置 ReportSheet = Sheets(MySheet)

2. 在设置对报告表的引用时，对对象使用完全限定.

   添加设置reportSheet = ThisWorkbook.Worksheets(mySheetName)

---

<块引用>

我尝试使用以下逻辑，但没有奏效，因为我无法插入 "作为命名的一部分以正确创建我的变量.

3. 要插入双引号字符，可以使用:char(34)

   例如TEST1 = "(" & Chr(34) & ThisWorkbook.Sheets("Sheet1").Range("C8").Value & ThisWorkbook.Sheets("Sheet1").Range(D8").Value & Chr(34) & )"

---

关于请求:

你可以用更短的方式来做这件事，但我选择了这条更长的路来说明这个想法.

1. 收集设置信息
2. 设置对列和行的引用
3. 找出它们之间的交集
4. 在该范围内查找非空单元格并隐藏它们的列和行

代码:

Public Sub DynamicallyHideCells()Dim settingsSheet 作为工作表将报告表变暗为工作表将 targetColumnsRange 作为范围变暗将 targetRowsRange 调暗为范围将目标范围调暗为范围将 targetCell 调暗为范围Dim reportSheetName As StringDim reportColumnsAddr As StringDim reportRowsAddr 作为字符串' 设置对设置表的引用设置 settingsSheet = ThisWorkbook.Worksheets("Sheet1")' 收集报表名称reportSheetName = settingsSheet.Range("C7").Value' 检查 : 两个单元格之间的引用reportColumnsAddr = settingsSheet.Range("C8").Value &:"&settingsSheet.Range("D8").Value' 检查 : 两个单元格之间的引用reportRowsAddr = settingsSheet.Range("C9").Value &:"&settingsSheet.Range("D9").Value' 设置对报表工作表的引用设置 reportSheet = ThisWorkbook.Worksheets(reportSheetName)' 设置对报告列的引用设置 targetColumnsRange = reportSheet.Range(reportColumnsAddr)' 设置对报告行的引用设置 targetRowsRange = reportSheet.Range(reportRowsAddr)' 查找要评估的单元格范围设置 targetRange = Intersect(targetColumnsRange, targetRowsRange)' 遍历每个单元格，如果不为空则隐藏对于每个 targetCell 在 targetRange.Cells如果 targetCell.Value <>vbNullString 然后targetCell.EntireColumn.Hidden = TruetargetCell.EntireRow.Hidden = True万一下一个目标单元格结束子

如果您只需要隐藏列.使用以下代码:

编辑 2:

添加用于检查输入单元格是否为空的行('检查任一单元格是否为空并退出子).

公共子隐藏列()Dim settingsSheet 作为工作表将报告表变暗为工作表将 targetColumnsRange 作为范围变暗Dim reportSheetName As StringDim reportColumnsAddr As String' 设置对设置表的引用设置 settingsSheet = ThisWorkbook.Worksheets("Sheet1")' 收集报表名称reportSheetName = settingsSheet.Range("C7").Value' 检查 : 两个单元格之间的引用reportColumnsAddr = settingsSheet.Range("C8").Value &:"&settingsSheet.Range("D8").Value'检查任一单元格是否为空并退出子如果 settingsSheet.Range(C8").Value = vbNullString 或 settingsSheet.Range(D8").Value = vbNullString 然后' 设置对报表工作表的引用设置 reportSheet = ThisWorkbook.Worksheets(reportSheetName)' 设置对报告列的引用设置 targetColumnsRange = reportSheet.Range(reportColumnsAddr)'隐藏范围内的列targetColumnsRange.EntireColumn.Hidden = True别的' 在这里做点什么万一结束子

希望这就是您要找的东西.

让我知道它是否有效.

I would like to hide certain columns and rows based on inputs into certain cells, to make the code easily editable.

Furthermore, if the reference cell/range is empty, then I would like to continue the code without hiding any rows or columns.

Columns to Hide: based on cells C8:D8

Rows to Hide: based on cells C9:D9

This is where the logical fest should take place

reportColumnsAddr = settingsSheet.Range("C8").Value & ":" & settingsSheet.Range("D8").Value

reportRowsAddr = settingsSheet.Range("C9").Value & ":" & settingsSheet.Range("D9").Value

Current Code

Option Explicit

Private Sub CommandButton1_Click()


Dim MyFolder As String, MyFile As String
Dim StartTime As Double
Dim MinutesElapsed As String
Dim Filename As String
Dim Cell As String
Dim Counter As Long


If ThisWorkbook.Sheets("Sheet1").Range("C7").Value = vbNullString Then

MsgBox "Enter Tab Name"
Exit Sub

End If

With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
.Title = "Select a Folder"
If .Show = True Then
MyFolder = .SelectedItems(1)

End If



If .SelectedItems.Count = 0 Then Exit Sub
Err.Clear
End With


Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

MyFile = Dir(MyFolder & "\", vbReadOnly)

StartTime = Timer

Do While MyFile <> ""
DoEvents
On Error GoTo 0

Workbooks.Open Filename:=MyFolder & "\" & MyFile, UpdateLinks:=False


Dim settingsSheet As Worksheet 'Source
Dim reportSheet As Worksheet 'To convert to PDF
 Dim targetColumnsRange As Range 'feeds from source
Dim targetRowsRange As Range
Dim reportSheetName As String 'source sheet with the target's sheet name
Dim reportColumnsAddr As String
Dim reportRowsAddr As String
' Set a reference to the settings sheet

Set settingsSheet = ThisWorkbook.Worksheets("Sheet1") ' source

' Gather the report sheet's name

reportSheetName = settingsSheet.Range("C7").Value ' good

reportColumnsAddr = settingsSheet.Range("C8").Value & ":" & settingsSheet.Range("D8").Value

reportRowsAddr = settingsSheet.Range("C9").Value & ":" & settingsSheet.Range("D9").Value

Set reportSheet = Sheets(reportSheetName) 

Set targetColumnsRange = reportSheet.Range(reportColumnsAddr)

Set targetRowsRange = reportSheet.Range(reportRowsAddr)

targetColumnsRange.EntireColumn.Hidden = True
targetRowsRange.EntireRow.Hidden = True


With reportSheet.PageSetup
.Zoom = False
.FitToPagesWide = 1 '.FitToPagesTall = 1
End With

Filename = ActiveWorkbook.Name

Cell = Replace(Filename, ".xlsx", ".PDF")

reportSheet.Select
reportSheet.PageSetup.Orientation = xlLandscape

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _

ThisWorkbook.Path & "\" & Cell, _

Quality:=xlQualityStandard, IncludeDocProperties:=True, _

IgnorePrintAreas:=True, OpenAfterPublish:=False

Counter = Counter + 1

0

Workbooks(MyFile).Close SaveChanges:=False

MyFile = Dir

Loop

'turns settings back on


Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
Application.Calculation = xlCalculationManual

MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")

MsgBox "Successfully Converted " & Counter & " Files in " & MinutesElapsed & " minutes", vbInformation

End Sub

解决方案

A couple of thoughts about your code:

Dim MySheet As String

1. As mySheet is referring to the sheet name, be clear about it.

   Rename the variable as Dim mySheetName as String

---

Set ReportSheet = Sheets(MySheet)

2. When setting the reference to the report sheet use full quilifying to the object.

   Add Set reportSheet = ThisWorkbook.Worksheets(mySheetName)

---

I tried using the following logic, but it did not work, since I'm not able to insert " as part of the naming to properly create my variable.

3. To insert a double quote character, you can use: char(34)

   e.g. TEST1 = "(" & Chr(34) & ThisWorkbook.Sheets("Sheet1").Range("C8").Value & ThisWorkbook.Sheets("Sheet1").Range("D8").Value & Chr(34) & ")"

---

Now about the request:

You could do this in a shorter way, but I chose this longer path, to illustrate the idea.

1. Gather the settings' information
2. Set a reference to both columns and rows
3. Find the intersection between them
4. Look for non empty cells in that range and hide their columns and rows

Code:

Public Sub DynamicallyHideCells()
    
    Dim settingsSheet As Worksheet
    Dim reportSheet As Worksheet
    
    Dim targetColumnsRange As Range
    Dim targetRowsRange As Range
    Dim targetRange As Range
    Dim targetCell As Range
    
    Dim reportSheetName As String
    Dim reportColumnsAddr As String
    Dim reportRowsAddr As String
    
    ' Set a reference to the settings sheet
    Set settingsSheet = ThisWorkbook.Worksheets("Sheet1")
    
    ' Gather the report sheet's name
    reportSheetName = settingsSheet.Range("C7").Value
    
    ' Check the : between the two cells reference
    reportColumnsAddr = settingsSheet.Range("C8").Value & ":" & settingsSheet.Range("D8").Value
    
    ' Check the : between the two cells reference
    reportRowsAddr = settingsSheet.Range("C9").Value & ":" & settingsSheet.Range("D9").Value
    
    ' Set a reference to the report's sheet
    Set reportSheet = ThisWorkbook.Worksheets(reportSheetName)
    
    ' Set a reference to the report's columns
    Set targetColumnsRange = reportSheet.Range(reportColumnsAddr)
    
    ' Set a reference to the report's rows
    Set targetRowsRange = reportSheet.Range(reportRowsAddr)
    
    ' Find the range of cells to be evaluated
    Set targetRange = Intersect(targetColumnsRange, targetRowsRange)
    
    ' Loop through each cell and hide if not empty
    For Each targetCell In targetRange.Cells
    
        If targetCell.Value <> vbNullString Then
            targetCell.EntireColumn.Hidden = True
            targetCell.EntireRow.Hidden = True
        End If
    
    Next targetCell
    
    
End Sub

EDIT:

If you only need to hide the columns. Use the following code:

EDIT 2:

Added line to check if input cells are empty (' Check if either cell are empty and exit sub).

Public Sub HideColumns()
    
    Dim settingsSheet As Worksheet
    Dim reportSheet As Worksheet
    
    Dim targetColumnsRange As Range
    
    Dim reportSheetName As String
    Dim reportColumnsAddr As String
    
    ' Set a reference to the settings sheet
    Set settingsSheet = ThisWorkbook.Worksheets("Sheet1")
    
    ' Gather the report sheet's name
    reportSheetName = settingsSheet.Range("C7").Value
    
    ' Check the : between the two cells reference
    reportColumnsAddr = settingsSheet.Range("C8").Value & ":" & settingsSheet.Range("D8").Value
    
    ' Check if either cell are empty and exit sub
    If settingsSheet.Range("C8").Value = vbNullString Or settingsSheet.Range("D8").Value = vbNullString Then
        
        ' Set a reference to the report's sheet
        Set reportSheet = ThisWorkbook.Worksheets(reportSheetName)
    
        ' Set a reference to the report's columns
        Set targetColumnsRange = reportSheet.Range(reportColumnsAddr)
    
        ' Hide the columns in range
        targetColumnsRange.EntireColumn.Hidden = True


    Else

        ' Do something here

    End If
    
    
End Sub

Hope this is what you're looking for.

Let me know if it works.

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