VBA Determining Blank Cell on Filter

[Littlemalky]

Hey, so I have my macro filtering by column "D", and if no data shows after the filter I need it to run a different filter; however, if that data is present then I need it to do something else. So the way I had originally set it up was like this:

Range("D1").Select
    ActiveCell.Offset(1, 0).Select
    If ActiveCell.Value = "" Then
        ActiveSheet.Range("A:E").AutoFilter Field:=5, Criteria1:="<0", Operator:=xlFilterValues, VisibleDropDown:=True
    Else
        Range("D2:D" & LastRow).Select
        Selection.Copy
        Workbooks("Fulfillment " & Format(Date, "yyyymmdd") & " - Fox" & ".xlsx").Worksheets("48hrs Exclusion").Activate
        ActiveSheet.Paste
        Range("A1").Select
        Selection.End(xlDown).Select
        ActiveCell.Offset(1, 0).Select
        Workbooks("Qry48hrsFox.xlsx").Activate
    End If

However, it's not working because apparently offsetting it literally selects the cell right below, not the one that is showing after the filter. Does anyone know how to set this up so I can reference the cell as if i were clicking the down arrow on my keyboard after selecting D1?

 

[JackDanIce]

Hi, see if you can modify this bit of code:

Sub Assimilation()
Application.ScreenUpdating = False
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
With Range("E1")
    .Select
    .AutoFilter Field:=1, Criteria1:="<3"
End With
Range(ActiveCell.Offset(1, 0), Cells(Rows.Count, ActiveCell.Column)) _
    .SpecialCells(xlCellTypeVisible)(1).Select
Application.ScreenUpdating = True
End Sub

Before code is run (E1 is selected)
Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 8pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 56px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>E</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>Test</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">3</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">3</TD></TR></TBODY></TABLE>

After code is run (E3 is selected):
Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 8pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 56px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>E</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>Test</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">3</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">3</TD></TR></TBODY></TABLE>

 

[Littlemalky]

I just grabbed that second part of it and plugged it in and it worked perfectly. Thank you so much for your help!