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.