Copy cell from filtered range, where is only one row

Question

I'm trying to copy data from column in filtered range. The number of filtered rows is always different. First row is header.

I'm using this

ThisWorkbook.Sheets(1).Range("N2:N" & ThisWorkbook.Sheets(1).Range("N" & ThisWorkbook.Sheets(1).Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy

This works fine, when the number of filtered rows is bigger than 1. But when there's only one filtered row (and it should be N2:N2), it will copy the whole used range.

Thanks for advice.

`AutoFilter` won't work well when there is no hits (you should filter from `N1` > assuming its the header). Maybe [this](https://stackoverflow.com/q/58889516/9758194) post gives you some insight. — JvdV, Jan 07 '20 at 12:05

score 1 · Answer 1 · answered Jan 07 '20 at 12:18

In order to correctly work with filtered data, you should follow this pattern:

Sub FilterRange()

    Dim rngTable As Range    '//Holds: header + data
    Dim rngData As Range     '//Holds: only data
    Dim rngFiltered As Range '//Holds: filtered range

    '// Our range (data + header)
    Set rngTable = [N1:N100]

    '// Get data only
    With rngTable
        Set rngData = .Offset(1).Resize(.Rows.Count - 1)
    End With

    '// Filter range
    rngTable.AutoFilter Field:=1, Criteria1:="1"

    '// Catch error if no values are filtered
    On Error Resume Next
    Set rngFiltered = rngData.SpecialCells(xlCellTypeVisible)

    '// Check if filtering was successful
    If Err = 0 Then
        '// Do some actions (for instance, copy to Sheet2)
        rngFiltered.Copy Sheets("Sheet2").Cells(1)
    Else
        '// No filtered range
    End If

    '// Get back to error raising
    On Error GoTo 0

End Sub

But what if I want to copy data only from one column? (Means one cell, beacuse it will filter only one row) — sprosut, Jan 07 '20 at 13:52
@sproust If you need first cell from filtered range, then you can call: `Set rngFirstCell = rngFiltered(1)` (if you have one column). — JohnyL, Jan 07 '20 at 13:55
@sproust If you have, say, five columns and you need to get first cell from third column, then: `Set rngFirstCell = rngFiltered.Cells(1, 3)`. — JohnyL, Jan 07 '20 at 13:56

score 0 · Accepted Answer · answered Jan 07 '20 at 12:52

You can test to ensure that there are more visible rows then the Header Row before copying your range. Set your range to a variable, then count the number of visible cells in the range to ensure there are rows to copy. Also, as JvdV pointed you should use N1 as the start. When copying visible cells in a range you should always use Offset to ensure you don't copy the Header Row, and Resize to ensure you don't copy the blank cell at the bottom due to the Offset. Here is a basic example of how to accomplish your task.

Dim rng As Range
Set rng = ThisWorkbook.Sheets(1).Range("D1:D" & ThisWorkbook.Sheets(1).Range("D" & ThisWorkbook.Sheets(1).Rows.Count).End(xlUp).Row)
    If rng.SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
        rng.Resize(rng.Cells.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible).Copy
    End If

Works even better in my case. Thanks you too. – sprosut Jan 09 '20 at 11:57 — sprosut, Jan 09 '20 at 11:57

Copy cell from filtered range, where is only one row

2 Answers2

Linked