castesting.blogg.se

End(xlUp) selects row 1 if there is no visible data, you may want to check which row this is first too!Īside: You really should be fully qualifying your ranges, i.e. Note, you're assuming there is even one row still visible. Set r = r.SpecialCells(xlCellTypeVisible) Set r = Range("B2", Range("B" & Rows.Count).End(xlUp)) Instead, set the range first, test if it only contains one cell, then proceed. Now we know what the problem is, we can avoid it! The line of code where you use SpecialCells: Set r = Range("B2", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible) If you apply Specialcells to only one cell, it actually applies to the entire used range of the sheet. Worksheets("For Slides").Range("P29").PasteSpecial Range(r(1), rC).SpecialCells(xlCellTypeVisible).Copy

Set r = Range("B2", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible) Worksheets("OLD_Master").Range("A:H").AutoFilter Field:=5, Criteria1:="Drinks" Worksheets("OLD_Master").Range("A:H").AutoFilter Field:=4, Criteria1:=Array( _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ Selection.sort Key1:=Range("H1"), Order1:=xlDescending, Header:=xlGuess, _ Worksheets("OLD_Master").Columns("A:H").Select Any ideas on why this code won't work when there is only one item? Sub top10() When I manually add one more item to this filter (total 2), then it copies it fine. I found that when there is only one item in column B after filtering, it doesn't copy that one cell - instead it copies the entire row and seems to be a strange selection.

I have been using this for many different filtered selection, but I came across a problem with one of my filter combinations. The following code applies filters and selects the top 10 items in column B after some filters are applied to the table.