Get the cell after filtering the data

Question

I have simple problem with my macro, I need to get the first cell after filtering the data. My header in excel is in Row 4 and the data is in Row 5 which is A5.

Now when I filter the data using my criteria it will show something like this,

The A5 is now hidden and the new is A13433. How get I can I get the cell A13433 dynamically. Because I have this code, Copying the filtered data to another sheet. Yes, This is working but when the cell A5 change. It causing a debug message.

 wsCopyQuery.Range("A5:U" & lDestRowDCB).SpecialCells(xlCellTypeVisible).Copy Destination:=wsDest.Range("A" & lDestRow)

As you can see in my code A5:... is static, and I want to dynamic that range, Something like this,

Dim getFilteredCell As Long
getFilteredCell = 'Code to get the filtered cell. For ex. "A13433"
wsCopyQuery.Range("A" & getFilteredCell & ":U" & lDestRowDCB).SpecialCells(xlCellTypeVisible).Copy Destination:=wsDest.Range("A" & lDestRow)

Something like that, Any Ideas? Thank you!

What do you mean, exactly, by *`when the cell A5 change. It causing a debug message.`* What is changing, and what is the debug.message, and what line does the message occur on? — Ron Rosenfeld, Apr 22 '20 at 16:57
^ Keep a static cell, you're using `SpecialCells` to get the visible cells anyway. — BigBen, Apr 22 '20 at 16:57
@RonRosenfeld, Yes after filtering the data, `A5` is hidden, In this line causing the error `wsCopyQuery.Range("A5:U" & lDestRowDCB).SpecialCells(xlCellTypeVisible).Copy` the error is `No cells where found.` Because `A5` is hidden. — , Apr 22 '20 at 17:01
@BigBen `lDestRowDCB = wsCopyQuery.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.count - 1` — , Apr 22 '20 at 17:16
That's the wrong way to get the last row; [this is the right way](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba). — BigBen, Apr 22 '20 at 17:18
So, do you need to obtain the first row/address of the filtered area, except the header? — FaneDuru, Apr 22 '20 at 18:40

FaneDuru · Answer 1 · 2020-04-23T07:28:12.867

You do not say anything, so I will post some code starting from the assumption that you need to copy a filtered range, starting from the first visible row (except the header), up to the last filtered cell (last filtered row, last column range:

Sub testFirstCelFilteredRange()
  Dim wsCopyQuery As Worksheet, wsDest As Worksheet, rng As Range
  Dim lDestRow As Long, lDestRowDCB As Long

   Set wsCopyQuery = ActiveSheet          ' use here your apropriate sheet
   Set wsDest = Worksheets("Destination") 'please, use here your sheet
   lDestRowDCB = wsCopyQuery.Range("A" & Rows.count).End(xlUp).Row
   lDestRow = wsDest.Range("A" & Rows.count).End(xlUp).Row + 1 

   Set rng = wsCopyQuery.Range("A4:U" & lDestRowDCB).SpecialCells(xlCellTypeVisible)

   Dim LastFilteredAreaAddress As String
   Debug.Print GetFirstFiltAddress(rng).Address' you can check here the first calculated cell of the range co be copied
   LastFilteredAreaAddress = rng.Areas(rng.Areas.count).Cells(rng.Areas(rng.Areas.count).Rows.count, _
                                            rng.Areas(rng.Areas.count).Columns.count).Address
   wsCopyQuery.Range(GetFirstFiltAddress(rng).Address & ":" & _
            LastFilteredAreaAddress).SpecialCells(xlCellTypeVisible).Copy wsDest.Range("A" & lDestRow)
End Sub

And this is the function returning the address of the first filtered cell, except the filtered area header:

Private Function GetFirstFiltAddress(fRng As Range) As Range
    Dim arrCount As Long, nRows As Long
    Const secRow As Long = 2
    Const firstCol As Long = 1
    With fRng
        Do
            arrCount = arrCount + 1
            nRows = nRows + .Areas(arrCount).Rows.count
        Loop While secRow > nRows And arrCount < .Areas.count

        If arrCount <= .Areas.count Then
            With .Areas(arrCount)
                If firstCol <= .Columns.count Then
                   Set GetFirstFiltAddress = .Item(firstCol)
                End If
            End With
        End If
    End With
End Function

Please confirm that it does what (I understood) you need...

score 0 · Answer 2 · answered Apr 22 '20 at 19:13

The following sets the variable R to the filtered range, so can then copy it to someplace else:

'Note that you hard code your first possible table row (hidden or not), and your first possible table column.
' It does assume that the rightmost cell of the first row has data. If that is not the case
' more information about your data setup would be needed.

Option Explicit
Sub due()
    Dim R As Range
    Dim wsCopyQuery As Worksheet: Set wsCopyQuery = Worksheets("Sheet3")
    Dim lLastRow As Long, lLastCol As Long
    Const lFirstRow As Long = 5
    Const lFirstCol As Long = 1

With wsCopyQuery
    lLastRow = .Cells(.Rows.Count, lFirstCol).End(xlUp).Row
    lLastCol = .Cells(lFirstRow, .Columns.Count).End(xlToLeft).Column
    Set R = .Range(.Cells(lFirstRow, lFirstCol), .Cells(lLastRow, lLastCol))
End With

'Debug.Print R.Address

Set R = R.SpecialCells(xlCellTypeVisible)
Debug.Print R.Address

End Sub

Get the cell after filtering the data

2 Answers2