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