Solved: Filtering Columns

[francis]

Hi All

I know that Excel doesn't have the built-in function for columns filter like
the rows filtering.

Is there a way to do it, perhap via a macro?

regards, francis

[Bob Phillips]

This will need quite a bit of code. I will have a go at it but it won't arrive today. Maybe someone else already has such a utility.

[david000]

This should work for the active cell anywhere in any row.



Sub RowSort()
Dim cel As Range
Set cel = ActiveCell.EntireRow

cel.Sort cel, xlAscending, , , , , , xlNo, , , xlSortRows

End Sub

[JKwan]

Is this what you mean, like a "horizontal" filter?? If so, try this out.
http://blog.livedoor.jp/andrewe/archives/2007-01.html

[Bob Phillips]

That was my first thought, to build a simple DV list, but it seems far more useful to me if you also allow the range of custom choices that autofilter allows.

[darthobra]

Hi,

My interpretation of this problem was that the dataset was a transposed table. Therefore copying the range and pasting it transposed in another worksheet and performing the filter and then reversing the process would provide the desired result.

Of course the code would need to be tweeked as I just recorded it as POC.


Sub ColumnFilter()
'
' ColumnFilter Macro
'
'
Sheets("Sheet3").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Sheet1").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2:A63") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("C2:C63") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:D63")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Selection.Copy
Sheets("Sheet3").Select
Selection.End(xlToLeft).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
End Sub


Darth

[francis]

Hi all

Thanks for taking the time in this. The link provided by JKwan does the
job although with one or two additional keystrokes.
Like xld have mentioned, a simple DV with filter is definitely more useful.

Once again, thank, your assistance is very much appreciate. I hope others will
also find this useful

regards, francis

[Cleaner007]

Hi!
Does anybody know about Clone Remover? It's said to be a good duplicate file removal tool. These guys here - moleskinsoft.com.