excel - Code to hide unhide columns working only when stepped through (F8) or run in isolation - Stack Overflow

admin管理员组

文章数量:1336663

I assigned a VBA macro to a button in Excel.
The code does work in isolation, and when stepped through.

Option Explicit

Dim iColumn As Long
Dim lastColumn As Long
Dim i As Long
Dim hdr As String
Dim columnsToHide() As Variant

Sub HideSpecificColumnsCAM()
    Call UnhideAllColumns
   
    columnsToHide = Array("Part Number", "Description")

    lastColumn = Cells(1, Columns.Count).End(xlToLeft).Column

    For iColumn = 1 To lastColumn
        For i = LBound(columnsToHide) To UBound(columnsToHide)
            hdr = columnsToHide(i)
            If Cells(1, iColumn) = hdr Then
                Cells(1, iColumn).EntireColumn.Hidden = True
                Exit For
            End If
        Next i
    Next iColumn
End Sub

Sub UnhideAllColumns()
    Dim col As Range
    Dim colrange As Range

    lastColumn = Cells(1, Columns.Count).End(xlToLeft).Column

    For iColumn = 1 To lastColumn
        Set colrange = WIP.Columns(iColumn)
        colrange.EntireColumn.Hidden = False
    Next iColumn
End Sub

When the macro is activated from within Excel nothing seems to happen.

I assigned a VBA macro to a button in Excel.
The code does work in isolation, and when stepped through.

Option Explicit

Dim iColumn As Long
Dim lastColumn As Long
Dim i As Long
Dim hdr As String
Dim columnsToHide() As Variant

Sub HideSpecificColumnsCAM()
    Call UnhideAllColumns
   
    columnsToHide = Array("Part Number", "Description")

    lastColumn = Cells(1, Columns.Count).End(xlToLeft).Column

    For iColumn = 1 To lastColumn
        For i = LBound(columnsToHide) To UBound(columnsToHide)
            hdr = columnsToHide(i)
            If Cells(1, iColumn) = hdr Then
                Cells(1, iColumn).EntireColumn.Hidden = True
                Exit For
            End If
        Next i
    Next iColumn
End Sub

Sub UnhideAllColumns()
    Dim col As Range
    Dim colrange As Range

    lastColumn = Cells(1, Columns.Count).End(xlToLeft).Column

    For iColumn = 1 To lastColumn
        Set colrange = WIP.Columns(iColumn)
        colrange.EntireColumn.Hidden = False
    Next iColumn
End Sub

When the macro is activated from within Excel nothing seems to happen.

• excel
• vba

Share Improve this question Follow edited Feb 16 at 11:46 CommunityBot 111 silver badge asked Dec 8, 2024 at 22:58 Luke KirbyLuke Kirby 122 bronze badges 8

• What do you mean by "from within Excel"? – GSerg Commented Dec 8, 2024 at 23:36
• 4 Only a single one of your Range objects is qualified by a Worksheet reference such that your code will always run with respect to the worksheet that is active in the UI; this won't matter if your code is only run from a button on the active sheet but will always matter when the code is run and the worksheet with the button is not active in the UI. – Spectral Instance Commented Dec 9, 2024 at 0:25
• 1 Expanding on @SpectralInstance comment, you need to know which sheet the code should process. Is it a specific sheet (such as WIP) or whatever sheet happens to be active when the code is run? In either case, quality all ranges with that sheet. – chris neilsen Commented Dec 9, 2024 at 5:19
• 1 Secondly, using module scoped variables here is dangerous. They might hold an unexpected value from a previous run of the code. Instead use locally scoped variables and pass them to subs/functions as required – chris neilsen Commented Dec 9, 2024 at 5:22
• 1 Thirdly See this re use of Call – chris neilsen Commented Dec 9, 2024 at 5:24

 |  Show 3 more comments

1 Answer 1

Sorted by: Reset to default 0

Try this out:

Sub HideSpecificColumnsCAM()
    Dim ws As Worksheet, lc As Long, colsToHide, rng As Range, c As Range
    
    Set ws = ActiveSheet 'or a specific worksheet
    
    lc = RowLastUsedColumn(ws.Rows(1))
    If lc = 0 Then Exit Sub 'nothing on this row
    
    colsToHide = Array("Part Number", "Description")
    
    Set rng = ws.Cells(1, "A").Resize(1, lc)
    For Each c In rng.Cells
        'hide/unhide according to match on `colsToHide`
        'Match returns an error value when `c.Value` is not in `colsToHide`
        c.EntireColumn.Hidden = _
           Not IsError(Application.Match(c.Value, colsToHide, 0))
    Next c
End Sub

'Find the column number of the last cell with a value on row `rw`
'  Fixes issue with `.End(xlToLeft)` skipping over hidden columns
'  Returns zero if `rw` has no content
Function RowLastUsedColumn(rw As Range) As Long
    Dim f As Range
    Set f = rw.Find("*", LookIn:=xlFormulas, searchorder:=xlColumns, _
                 SearchDirection:=xlPrevious)
    If Not f Is Nothing Then RowLastUsedColumn = f.Column
End Function

本文标签： excelCode to hide unhide columns working only when stepped through (F8) or run in isolationStack Overflow