PDA

View Full Version : Auto Filter



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

Bob Phillips
05-11-2007, 06:26 AM
Private Sub cmdProcess_Click()
Dim i As Integer
Dim j As Integer
For j = 4 To CountVisCols
Flag = True
For i = 2 To CountVisRows
If ActiveSheet.Rows(i).Hidden = False Then
If ActiveSheet.Cells(i, j).Value <> "<>" Then
Flag = False
Exit For
End If
End If
Next
If Flag = True Then
Columns(j).EntireColumn.Hidden = True
Flag = False
End If
Next
End Sub

johnske
05-11-2007, 06:40 AM
Hi milind, I've put code tags around your code to make it readable - go to your post (#1) and click edit to see how it's done :)

Bob Phillips
05-11-2007, 06:44 AM
And you indented it ... that is above and beyond :yes

milind
05-11-2007, 08:43 AM
still having problem. Problem is that it is hidding all columns Thing is that after filtering we want to hidden those colums whoes data is like this "<>" in all rows

Bob Phillips
05-11-2007, 08:48 AM
You'll need to post an example, because that is what my testing did.

milind
05-11-2007, 08:53 AM
i have attach file for example

Bob Phillips
05-11-2007, 09:35 AM
It is your CountVisRows routine that is the proble.



Private Sub cmdProcess_Click()
Dim i As Integer
Dim j As Integer

For j = 4 To CountVisCols
Flag = True
For i = 2 To Me.Cells(Me.Rows.Count, j).End(xlUp).Row
If Me.Rows(i).Hidden = False Then
If Me.Cells(i, j).Value "" Then
Flag = False
Exit For
End If
End If
Next
If Flag = True Then
Me.Columns(j).EntireColumn.Hidden = True
Flag = False
End If
Next
End Sub

milind
05-11-2007, 06:58 PM
Thank you it works out