Excel VBA隐藏列报错：Range对象调用失败及应用定义错误求助

Here's the corrected code with explanations of the key fixes:

Private Sub IWasRunning()

Dim wbSource As Workbook
Dim wbTarget As Workbook
Dim shSource As Worksheet
Dim shTarget As Worksheet
Dim shHelper As Worksheet
Dim Rnga As Range
Dim Rngb As Range
Dim Rngc As Range
Dim Stations As Variant
Dim i As Integer

Set wbSource = Workbooks.Open(Filename:="E:\DE Daily Production Plan\A. Production Plans\(B. 1 of 2) Current Production Plans\Blue Line Schedule.XLSX", ReadOnly:=True)
Set wbTarget = Workbooks.Open(Filename:="E:\DE Daily Production Plan\A. Production Plans\(B. 1 of 2) Current Production Plans\Schedule Assist.XLSM", ReadOnly:=True)
Set shSource = wbSource.Worksheets("Schedule")
Set shTarget = wbTarget.Worksheets("DailyPrintable")
Set shHelper = wbTarget.Worksheets("Start")
Set Rnga = shHelper.Range("A1")
Set Rngb = shHelper.Range("B1")

' Fix 1: Use Union to combine non-contiguous ranges (resolved Runtime Error 1004)
Set Rngc = Union(shTarget.Range("A:B"), _
                 shTarget.Range("F:F"), _
                 shTarget.Range("H:L"), _
                 shTarget.Range("N:AD"))

wbTarget.Names.Add Name:="SKE", RefersTo:=Rnga
wbTarget.Names.Add Name:="Daily", RefersTo:=Rngb

Application.ScreenUpdating = False

Stations = Array("3", "8", "13", "18", "23", "28")
For i = 0 To UBound(Stations)
    ' Fix 2: Qualify named ranges with wbTarget and use .Value to get cell content
    ' Fix 3: Replace string "3" with numeric 3 to avoid type mismatch
    shSource.Rows(wbTarget.Range("SKE").Value + 2 - i).Rows("1:" & wbTarget.Range("Daily").Value).Copy
    shTarget.Rows(wbTarget.Range("Daily").Value * i + (3 + i)).PasteSpecial Paste:=xlPasteAllUsingSourceTheme
Next i

Application.CutCopyMode = False

' Fix 4: Simplify row range reference (removed redundant Rows(2).Rows(...))
With shTarget.Rows("2:" & (2 + wbTarget.Range("Daily").Value * 8 - 1)).Font
    .Name = "Calibri"
    .Size = 24
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .TintAndShade = 0
    .ThemeFont = xlThemeFontNone
End With

' Fix 5: Simplify autofit range and remove redundant Columns.ColumnWidth
With shTarget.Rows("2:" & (2 + wbTarget.Range("Daily").Value * 8 - 1))
    .Columns.AutoFit
    .RowHeight = 25
End With
shTarget.Columns(4).ColumnWidth = 25 ' Set specific column width separately

' Fix 6: Use the Rngc variable directly (not as a named range string)
With Rngc
     .EntireColumn.Hidden = True
End With

Application.ScreenUpdating = True
End Sub

Key Fixes Explained: #

1. Combining Non-Contiguous Ranges:
   The original Set Rngc = shTarget.Range("A:B", "F:F", "H:L", "N:AD") throws an error because the Range method doesn't accept multiple range addresses like this. Use Union() to merge separate range objects into one.

2. Accessing the Range Variable:
   shTarget.Range("Rngc") is incorrect because Rngc is a VBA variable, not a named range in the worksheet. Use the variable name directly (Rngc) without quotes.

3. Qualified Range References:
   Unqualified Range("SKE") or Range("Daily") can reference the wrong sheet. Always qualify named ranges with their parent workbook (wbTarget.Range("SKE")) and use .Value to retrieve the cell's numeric value.

4. String-Numeric Type Mismatch:
   "3" + i tries to add a string and integer, causing a type error. Replace "3" with numeric 3.

5. Simplified Range Syntax:
   Redundant references like shTarget.Rows(2).Rows("1:X") are replaced with a clear row range ("2:" & (2 + ... -1)) to avoid confusion.

6. Cleaner Autofit Logic:
   Moved the column width setting outside the With block to avoid redundant Columns.ColumnWidth syntax.

内容的提问来源于stack exchange，提问作者JR Stroik