milind
05-11-2007, 05:45 AM
I have worksheet with some records. And I filter that records. Now some columns have this "<>" type of data. So what i want is that if that type of data in whole column then it should be hide. for that I am using following codes.
Dim Flag As Boolean
Private Sub cmdProcess_Click()
Dim i As Integer
Dim j As Integer
For j = 4 To CountVisCols
For i = 2 To CountVisRows
If ActiveSheet.Cells(i, j).Value <> "<>" Then
Flag = False
Exit For
Else
Flag = True
End If
Next
If Flag = True Then
Columns(j).Select
Selection.EntireColumn.Hidden = True
Flag = False
End If
Next
End Sub
Function CountVisRows() As Integer
Rem By Pranav
Rem It is use for counting visible rows.
Dim rng As Range
Set rng = ActiveSheet.AutoFilter.Range
CountVisRows = rng.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1
End Function
Function CountVisCols() As Integer
Rem By Pranav
Rem It is use for counting visible rows.
Dim rng As Range
Set rng = ActiveSheet.AutoFilter.Range
CountVisCols = rng.Columns.Count
End Function
cmdProcess is the button Now problem is that it is counting unvisible(unfiletered) row also. so what is the change that we can do???
Dim Flag As Boolean
Private Sub cmdProcess_Click()
Dim i As Integer
Dim j As Integer
For j = 4 To CountVisCols
For i = 2 To CountVisRows
If ActiveSheet.Cells(i, j).Value <> "<>" Then
Flag = False
Exit For
Else
Flag = True
End If
Next
If Flag = True Then
Columns(j).Select
Selection.EntireColumn.Hidden = True
Flag = False
End If
Next
End Sub
Function CountVisRows() As Integer
Rem By Pranav
Rem It is use for counting visible rows.
Dim rng As Range
Set rng = ActiveSheet.AutoFilter.Range
CountVisRows = rng.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1
End Function
Function CountVisCols() As Integer
Rem By Pranav
Rem It is use for counting visible rows.
Dim rng As Range
Set rng = ActiveSheet.AutoFilter.Range
CountVisCols = rng.Columns.Count
End Function
cmdProcess is the button Now problem is that it is counting unvisible(unfiletered) row also. so what is the change that we can do???