PDA

View Full Version : Hiding sepecific column on based of condition



ravinder_tig
06-09-2009, 01:20 AM
Hi Guys
I do need your help regarding one of projects in which i had to hide a certain column on a specific condition
in this case column heading 3,4(G,H) and 7,8(K,L) should be hide
the function i created for this is it checks the range for value "N/A" only for Visible columns
if it matches a value which is
Number of visible columns - 3 (as data start from column 4) - 6 number of hidden columns
but the problem is i'm not able to deal with hidden condition
i'm attaching the file as well function


Public Function colhide()
Dim i As Integer, _
c As Integer
Dim MyLastRow As Long, _
MyLastCol As Long
Dim MyRange2 As String, _
MyLastCell As String, _
Str As String, _
MyColLett As String
Dim Cell As Range
With Worksheets("All Summary")
Application.DisplayAlerts = False

On Error Resume Next
MyLastRow = .Cells(Rows.Count, "D").End(xlUp).Row
MyLastCol = .Cells(3, Columns.Count).End(xlToLeft).Column
For c = 5 To MyLastCol
i = 0
Str = .Cells(4, c).Address
MyColLett = Split(Columns(c).Address(False, False), ":")(0)
MyRange2 = Str & ":" & "$" & MyColLett & "$" & MyLastRow
For Each Cell In Range(MyRange2)
If InStr(Cell.Value, "N/A") <> 0 And .Cells(c, MyColLett).Hidden = False Then
i = i + 1
End If
Next
If i = MyLastRow - 3 - 6 Then ' Where 6 is number of hidden columns (Variale for diff sheets)
MsgBox ("Column is hidden")
'ActiveSheet.Range(MyRange2).EntireColumn.Hidden = True
End If
Next
Application.DisplayAlerts = True
End With
End Function

Your help would be gr8ly appriciated
Regards,
Ravinder S

mdmackillop
06-09-2009, 10:32 AM
I'm not really clear what you are after. It is unneccesary to check each row in a column. Do you need more than this?


Sub TestHidden()
Dim MyRange As Range
Set MyRange = Range(Cells(3, 5), Cells(3, Columns.Count).End(xlToLeft))
For Each cel In MyRange
If cel.EntireColumn.Hidden = True Then MsgBox "Column is hidden"
'or if only some columns to be identified
Select Case cel.Column
Case 7, 8, 11, 12
If cel.EntireColumn.Hidden = True Then MsgBox "Column " & cel.Column & " is hidden"
End Select
Next
End Sub

ravinder_tig
06-09-2009, 06:24 PM
thanks a lot mdmackillop (http://www.vbaexpress.com/forum/member.php?u=87) vbmenu_register("postmenu_187357", true); but my problem is i don't hav fix columns in this case i was jst giving the example that i had to hide certain column7,8,11,12

what i had to do is match the range below only for visible rows if that range is full of "N/A" column should hide

mdmackillop
06-10-2009, 03:06 PM
Sub TestHidden()
Dim MyRange As Range
Dim c As Range, cel as Range
Set MyRange = Range(Cells(3, 5), Cells(3, Columns.Count).End(xlToLeft))

For Each cel In MyRange
Set c = cel.Offset(1).Resize(21).SpecialCells(xlCellTypeVisible)
If Application.CountIf(c, "N/A") = c.Cells.Count Then
cel.EntireColumn.Hidden = True
MsgBox "Column " & cel.Column & " is hidden"
End If
Next
End Sub