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
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