Hiding and unhiding columns using vba?

  • Thread starter Thread starter Rick
  • Start date Start date
R

Rick

Hi all...

How is it possible to hide columns using vba code? I would like to conditionally hide and unhide
columns.

Thank you,

Rick
 
Hi
try the following macro to conditional hide rows
Hi Bryan
try the following macro

Private Sub hide_row()
Dim r As Long
Application.ScreenUpdating = False
With Worksheets("Sheet1")
For r = .UsedRange.Rows(.UsedRange.Rows.Count).Row To 1 Step -1
If Application.CountA(Range(Cells(r,"A"),Cells(r,"O"))) _
= "X" Then
.Rows(r).Hidden = True
End If
Next
End With
Application.ScreenUpdating = True
End Sub

Hides all rows with a "X" in column A
 
Hi Rick

Once you understand the simple Boolean logic for hiding rows/columns
it's very easy. Consider the macro below wich will hide all column where
the row 1 cell contains the word "Hide" and unhide all columns where the
row 1 cell does NOT contain "Hide"

Sub HideShow()
Dim i As Integer
For i = 1 To 256
Columns(i).Hidden = _
UCase(Columns(i).Cells(1, 1)) = "HIDE"
Next i
End Sub


***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****
 
In addition to the others, this might come in handy

Sub HideG_UnhideG()
If Columns("g").EntireColumn.Hidden = True Then
Columns("g").EntireColumn.Hidden = False
ActiveSheet.Shapes("toggleit").Select
Selection.Characters.Text = "HIDE G" & Chr(10) & "" & Chr(10) & ""
Range("c3").Select
Else
Columns("g").EntireColumn.Hidden = True
ActiveSheet.Shapes("Toggleit").Select
Selection.Characters.Text = "SHOW G" & Chr(10) & "" & Chr(10) & ""
End If
Range("c3").Select

'can use =not if only ONE change.....
'Columns("g").EntireColumn.Hidden = Not Columns("g").EntireColumn.Hidden
End Sub
 
Don said:
In addition to the others, this might come in handy

Sub HideG_UnhideG()
If Columns("g").EntireColumn.Hidden = True Then
Columns("g").EntireColumn.Hidden = False
ActiveSheet.Shapes("toggleit").Select
Selection.Characters.Text = "HIDE G" & Chr(10) & "" & Chr(10) & ""
Range("c3").Select
Else
Columns("g").EntireColumn.Hidden = True
ActiveSheet.Shapes("Toggleit").Select
Selection.Characters.Text = "SHOW G" & Chr(10) & "" & Chr(10) & ""
End If
Range("c3").Select

'can use =not if only ONE change.....
'Columns("g").EntireColumn.Hidden = Not Columns("g").EntireColumn.Hidden
End Sub
Click to expand...
Are hidden Worksheets and Columns still available by using an index. For example if I hide
Worksheet(3) can it still be accessed by using the index 3 or is it now out of the Worksheet array?

Thanks for everyone's help!

Rick
 
You can see the answers to these kinds of questions with a little bit of
testing:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim newWkbk As Workbook
Dim iCtr As Long

Set newWkbk = Workbooks.Add(1) 'single worksheet

With newWkbk
'add some sheets
For iCtr = 2 To 10
.Worksheets.Add after:=.Worksheets(.Worksheets.Count)
Next iCtr

'hide some sheets
For iCtr = 1 To .Worksheets.Count
If iCtr > 4 _
And iCtr < 8 Then
.Worksheets(iCtr).Visible = xlSheetHidden
End If
Next iCtr

'what's there via the index?
For iCtr = 1 To .Worksheets.Count
MsgBox .Worksheets(iCtr).Name
Next iCtr
End With

End Sub
 
Back
Top