Posts by joeybalboa

joeybalboa · May 19th 2017

Re: xlCellTypeVisible capturing non-visible cells for deletion

I seem to have figured out a solution myself...... I've just added an IF statement at the start of the sub to check if the Column A "hide" column is empty... if empty it exits the sub.

joeybalboa · May 19th 2017

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.

Code

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

Display More