views:

33

answers:

0

Hey all,

I would like to use a for each loop in excel to copy only all visible values in my excel spreadsheet in order to remove the link from the auto filtering so that when I export or copy these values, the hidden values from the auto filter don't come back.

Here's macro:

Sub sortify()

Application.ScreenUpdating = False

Range("A1:E519").Select
Selection.Cut Destination:=Range("A2:E520")
Range("A2:E520").Select

Range("A1").Select
ActiveCell.FormulaR1C1 = "A "
Range("B1").Select
ActiveCell.FormulaR1C1 = "B"
Range("C1").Select
ActiveCell.FormulaR1C1 = "C"
Range("D1").Select
Range("D1").Select
ActiveCell.FormulaR1C1 = "D"
Range("E1").Select
ActiveCell.FormulaR1C1 = "E"
Range("A1").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$E$520"), , xlYes).Name = _
    "Table1"
Range("Table1[#All]").Select
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight8"
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= _
    "=Real Prop*", Operator:=xlAnd
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=2, Criteria1:= _
    "=DF*", Operator:=xlAnd

ActiveSheet.ListObjects("Table1").Sort.SortFields.Clear
ActiveSheet.ListObjects("Table1").Sort.SortFields.Add _
    Key:=Range("Table1[[#All],[E]]"), SortOn:=xlSortOnValues, Order:= _
    xlAscending, DataOption:=xlSortNormal
With ActiveSheet.ListObjects("Table1").Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
Columns("C:E").Select
Selection.Cut
Selection.SpecialCells(xlCellTypeVisible).Select
Range("F1").Select
ActiveSheet.Paste

Columns("A:E").Select
Selection.EntireColumn.Hidden = True

Rows("1:1").Select
Range("F1").Activate
Selection.EntireRow.Hidden = True

For Each SpecialCells(xlCellTypeVisible)
//now copy only visible cells to isolate them from any auto filtering 
End Sub

Thanks for any response.