Auto Filter

[milind]

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.

[VBA]
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
[/VBA]


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]

[vba]

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
[/vba]

[johnske]

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]

And you indented it ... that is above and beyond

[milind]

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]

You'll need to post an example, because that is what my testing did.

[milind]

i have attach file for example

[Bob Phillips]

It is your CountVisRows routine that is the proble.

[vba]

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
[/vba]

[milind]

Thank you it works out