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