0

I need to filter some data based on duration (column) and copy the ids (other column) of the result to another sheet.

I have tried the following, but I always get 1 as the amount of filtered rows (actually it's over 100) and an error on the copy attempt:

dataSheet.UsedRange.AutoFilter Field:=durationColIndex, Criteria1:="<100" 

Dim quickRange As Range, quickCount As Long
Set quickRange = dataSheet.UsedRange.SpecialCells(xlCellTypeVisible)
quickCount = quickRange.Rows.Count  '<----- does not work, is always 1

If quickCount > 0 Then
    Set specialIdsSheet = Sheets.Add    'defined before
    
    'copy col 1
    quickRange.Range(Cells(1, idIndex), Cells(25, idIndex)).Copy  '<-- other issue. 25 to substitue the not working quickCount
    specialIdsSheet.Cells(1, 1).PasteSpecial
 End If

I have tried to work around it for some hours now, but I stuck and don't understand why it is not working. I had a solution before that was using ActiveSheet and Select, but I wanted to move away from that.

   ActiveSheet.Range("A1").CurrentRegion.Range(Cells(1, idIndex), Cells(ActiveCell.CurrentRegion.Rows.Count, idIndex)).Select
    Selection.Copy
    Sheets.Add After:=ActiveSheet

Thanks in advance!

lora
  • 1
  • idIndex =1, by the way. It's defined before – lora May 27 '21 at 00:05
  • Presumably `quickRange` is a multi-area range. Using `.Rows` or `.Columns` on a multi-area range only returns the rows/columns of the first area. – BigBen May 27 '21 at 00:07
  • `quickRange.Range(Cells(1, idIndex), Cells(25, idIndex)).Copy` is problematic because the inner `Cells` implicitly refer to the `ActiveSheet` and not to `dataSheet`. Though, `quickRange.Range` seems problematic too, i.e. why use `Range` on a `Range`? – BigBen May 27 '21 at 00:10
  • Honestly it seems like the approach in [this answer](https://stackoverflow.com/a/33264772/9245853) is much simpler. – BigBen May 27 '21 at 00:13
  • @BigBen If I read the code you linked right, it copies the whole filter result, but I only want to copy one column of it. `quickRange.Range(quickRange.Cells(1, idIndex).... ` does not work either. The bad thing is that I don't understand why xD – lora May 27 '21 at 07:43
  • You can use `Intersect` along with the approach in the linked question to restrict to just one column. – BigBen May 27 '21 at 12:54

0 Answers0