VB Help

ryan01 · Aug 16, 2011

Hi guys

Have a tad problem. I have a vb code to hide and unhide a set of columns. The sheets are split into Term 1 and Term 2. I have a userform button for each hide and unhide. Problem is, I might need to insert columns for the terms. If I do, the vb doesn't update. How do I correct that?

Code:
 Macro for hide/unhide t2
Sub HideT2()
'
' HideT2 Macro
'

'
ActiveSheet.Unprotect
Range("G1:J1").Select
Selection.ColumnWidth = 0
Range("Q1:V1").Select
Selection.ColumnWidth = 0
Range("AE1:AL1").Select
Selection.ColumnWidth = 0
Range("AR1:AV1").Select
Selection.ColumnWidth = 0
Range("BC1:BH1").Select
Selection.ColumnWidth = 0
Range("BW1:CN1").Select
Selection.ColumnWidth = 0
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("D8").Select

End Sub
Sub UnhideT2()
'
' UnhideT2 Macro
'
ActiveSheet.Unprotect
Range("G1:CN1").Select
Selection.ColumnWidth = 3.83
Range("Q1:V1").Select
Selection.ColumnWidth = 3.83
Range("AE1:AL1").Select
Selection.ColumnWidth = 3.83
Range("AR1:AV1").Select
Selection.ColumnWidth = 3.83
Range("BC1:BH1").Select
Selection.ColumnWidth = 3.83
Range("BW1:CN1").Select
Selection.ColumnWidth = 3.83
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("D8").Select

End Sub

Thanks for the help

Sthrncali · Aug 16, 2011

VB will not automatically update code references with the addition/removal of rows/columns on a worksheet. The only way to deal with dynamic columns in VBA would be to identify the column in real time, as opposed to hard coding your VBA to always deal with a specific column.

For example, if the column you wanted to move had a header that said "MyCol" and you knew that value would always be in Row 1 of that column, and would not occur in any other columns, you could write your VBA to move the column with that header, regardless of what column that may be..

Example.

Code:

Dim Col as Long
My myRange as Range
Set myRange = Sheet1.Range("A1:D1")
 
For each cell in myRange
if cell.value = "myCol" then
Col = cell.column
Sheet1.Columns(Col).ColumnWidth = 0
end if
Next

VB Help

ryan01

New Member

Excel Facts

Sthrncali

Board Regular

Similar threads

Forum statistics

Share this page

VB Help

ryan01

New Member

Excel Facts

Sthrncali

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock