using resize ?

[Danny54]

I have the following code which sets a range based on data being in the last column, however after running the autofilter a few lines later, the rows meeting the criteria have no data in the last column and are not copied to the new sheet.

How could i get all the records after the autofilter is applied?

My thought would be to change the for each and use column a to determine how many rows are in the autofilter
then do a resize before the copy??

Suggestions would be appreciated.

Thanks



For Each rfl In .Range(.Cells(1, .Columns.Count), .Cells(.Rows.Count, .Columns.Count).End(xlUp))

collection = rfl.Text
Set wsNew = Workbooks.Add
sfilename = "Trending " & collection & ".xlsx"
ActiveWorkbook.SaveAs opath & "" & sfilename
Application.DisplayAlerts = False
ws.Activate

rData.AutoFilter Field:=1, Criteria1:=collection

rData.Copy

Windows(sfilename).Activate
ActiveCell.PasteSpecial Paste:=xlPasteValues
ActiveWorkbook.Close SaveChanges:=True

rData.AutoFilter Field:=1 'reset selection to show all

 

[James006]

Hello,

You could test with a new variable rFiltered ...

Set rFiltered = rData.SpecialCells(xlCellTypeVisible)

Hope this will help