Hi All! I picked up a piece of code off a forum for using the Advanced Filter xfiltercopy to cut from one sheet to another based on criteria, instead of the usual copy.
Context: If I specify any value in column A on my main data sheet, I want the macro to cut those marked rows to another hidden sheet. Then on that hidden sheet, I need it to paste below the last record, not overwrite the existing data that exists there (keeps a running list of my hidden data). Its working great when I specify a value in column A so there is data to exclude, however when I run the macro when I haven't specified a value in any row on Column A (which is a possibility I need to account for), it deletes the whole data set?
Data headers start at row 4 on my "Data" sheet, the Advanced Filter criteria in cells A1:A2 are just an * to capture any value in column A.
Dim rTable As Range, r As Range Dim maindatalength, hiddenlength As Long maindatalength = Worksheets("Data").Range("B" & Rows.Count).End(xlUp).Row Set rTable = Worksheets("Data").Range("A4:AM" & maindatalength) hiddenlength = Sheets("Hidden").Range("B" & Rows.Count).End(xlUp).Row + 1 ' filter and copy rTable.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Worksheets("Data").Range("A1:A2"), CopyToRange:=Worksheets("Hidden").Range("A" & hiddenlength & ":AM" & hiddenlength) Worksheets("Hidden").Rows(hiddenlength).EntireRow.Delete '//deletes my header row that gets copied to the Hidden sheet, couldn't find another way to paste on the next available row without it pasting the headers ' filter in place rTable.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Worksheets("Data").Range("A1:A2") ' exclude the header row Set r = rTable.Resize(rTable.Rows.Count - 1).Offset(1) ' get the visible rows On Error Resume Next Set r = r.SpecialCells(xlCellTypeVisible) '//problem line of code, seems to be capturing hidden rows On Error GoTo 0 Worksheets("Data").ShowAllData ' delete them If Not r Is Nothing Then r.Delete shift:=xlShiftUp '//problem line of code, this is where my whole data set gets deleted