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
- ' 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