when deleting table, filtered out items reappear in columns copied from table

johnmerlino

Board Regular
Joined
Sep 21, 2010
Messages
94
Hey all,

When I copy columns from a table to new columns, while it only copies over the filtered rows, when I delete the table, this forces the filtered out cells from the table to reappear in the newly created columns. This is not good because the whole purpose of filtering certain cells out was so I can isolate a list in a new column. Is there a way to break any link to table so I can only keep the copied values and not the hidden values that existed in the table?

Here's macro:

Code:
Sub sortify()
'
' sortify Macro
'
' Keyboard Shortcut: Ctrl+Shift+S
'
    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
End Sub

Thanks for any response.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

johnmerlino

Board Regular
Joined
Sep 21, 2010
Messages
94
So there is no way to remove the association between table and contents that were copied from the table?
 

Forum statistics

Threads
1,144,583
Messages
5,725,132
Members
422,590
Latest member
Mikeyyy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top