admin管理员组文章数量:1327750
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.
Share Improve this question edited Feb 16 at 11:46 CommunityBot 11 silver badge asked Dec 8, 2024 at 22:58 Luke KirbyLuke Kirby 12 bronze badges 8 | Show 3 more comments1 Answer
Reset to default 0Try 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
版权声明:本文标题:excel - Code to hide unhide columns working only when stepped through (F8) or run in isolation - Stack Overflow 内容由网友自发贡献,该文观点仅代表作者本人, 转载请联系作者并注明出处:http://www.betaflare.com/web/1742226946a2436497.html, 本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容,一经查实,本站将立刻删除。
Range
objects is qualified by aWorksheet
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