我想根据某些单元格的输入隐藏某些列和行，以使代码易于编辑。
此外，如果引用单元格/范围为空，那么我希望在不隐藏任何行或列的情况下继续代码。
要隐藏的列：基于单元格C8:D8
要隐藏的行：基于单元格C9:D9
这是逻辑节日应该发生的地方
reportColumnsAddr = settingsSheet.Range("C8").Value & ":" & settingsSheet.Range("D8").Value
reportRowsAddr = settingsSheet.Range("C9").Value & ":" & settingsSheet.Range("D9").Value
当前代码
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
关于您的代码的一些想法：
将MySheet Dim为字符串
-
由于
mySheet指的是工作表名称，请明确它。将变量重命名为
Dim mySheetName as String
Set ReportSheet=Sheets(MySheet(
-
设置对报表的引用时，请对对象使用完全平衡。
添加
Set reportSheet = ThisWorkbook.Worksheets(mySheetName)
我尝试使用以下逻辑，但它不起作用，因为我无法插入"；作为命名的一部分，以正确地创建我的变量。
-
要插入双引号字符，可以使用：
char(34)例如
TEST1 = "(" & Chr(34) & ThisWorkbook.Sheets("Sheet1").Range("C8").Value & ThisWorkbook.Sheets("Sheet1").Range("D8").Value & Chr(34) & ")"
现在关于请求：
你可以用更短的方式来做这件事，但我选择了更长的路径来说明这个想法。
- 收集设置信息
- 设置对列和行的引用
- 找到它们之间的交叉点
- 查找该区域中的非空单元格并隐藏其列和行
代码：
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
编辑：
如果您只需要隐藏列。使用以下代码：
编辑2:
添加了一行以检查输入单元格是否为空("检查任一单元格是否为空白并退出子单元格"(。
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
希望这就是你想要的。
如果有效，请告诉我。
将"替换为"：
TEST1 = "(" & """" & ThisWorkbook.Sheets("Sheet1").Range("C8").Value & ThisWorkbook.Sheets("Sheet1")
.Range("D8").Value & """" & ")"