(Solved) – Cannot Find the Top Row After Autofiltering

  • by

I have one workbook with two worksheets in it. What I am trying to do is autofilter through a range of unique values that I extracted and placed in a column in the far off reaches of both sheets. I’m then looking to copy and paste the values in the first three columns of the first visible row of what was filtered. I’m including the code below as I’ve tried a number of ways to approach this, but regardless of what method I use, whether to find the row number of the first row after filtering, using the code below with the union application, or a combination of the two what seems to happen is that when I run the filter below for “Slate Data” and then copy and paste it into the second sheet (“Placement Outlook”) always results in whatever is in row 2 on “Slate Data” actual to be copy and pasted in the rows in “Placement Outlook”.

The interesting and frustrating part about this is that I will get the desired result when I manually go through the autofilter and separately run the code for copying and pasting below, it’s only when that code interacts with the autofilter code that it continues to only always copy and paste row 2 of the “Slate Data” worksheet into the “Placement Outlook” worksheet.

Any help is greatly appreciated!!

Dim lastrowuic, lastrowarray As Long
lastrowuic = Cells(Rows.Count, "C").End(xlUp).Row
lastrowarray = Cells(Rows.Count, "BP").End(xlUp).Row
Dim uniquesarray As Variant
uniquesarray = Range("C2:C" & lastrowuic)
Dim sdws As Worksheet
Set sdws = ActiveWorkbook.Sheets("Slate Data")
Dim pows As Worksheet
Set pows = ActiveWorkbook.Sheets("Placement Outlook")
Dim firstcell, middlecell, lastcell As Range
Set firstcell = sdws.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 1)
Set middlecell = sdws.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 2)
Set lastcell = sdws.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 3)
Set homeport_command_uic = Application.Union(firstcell, middlecell, lastcell)
Dim uic As Integer
uic = 2

Do While sdws.Cells(uic, 68).Value <> ""
    sdws.Range("A1").CurrentRegion.AutoFilter Field:=3, Criteria1:=sdws.Cells(uic, 68)
        If lastcell <> "" Then
        homeport_command_uic.Copy pows.Cells(uic, 2).Offset(1, 0)
        End If

uic = uic   1

End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *