VBA Filter Blank Cells filters ALL cells in table

[Xipha]

I am using this line of code

ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:="<>"

Which according to all the forums should filter out all (and only) the blank values. On my sheet it filters ALL of the cells in the table so there are no rows showing, despite having ~50 with a value in them. Could this be due to the cells having a formula in them and not just a value? Any suggestions on how to fix this problem?

 

[Fluff]

That will only filter blank cells, regardless of whether they have a formula or not.
If it's hiding all rows, then there is something else going on.

 

[Xipha]

When I used " = " as the criteria it also filtered out all of the rows

 

[Fluff]

What formula do you have in the first column of your table?

 

[Xipha]

IFERROR(INDEX(Results,MATCH($A6,LabIDRange,0),MATCH(SoilSalinity[[#Headers],[Sample ID]],Results[#Headers],0)),"")

It's a table that is a subset of data from a master table that I am trying to automate the print setup. So there is a pivot table that updates with sample ID numbers that can be filtered based on certain parameters, and then the table adjacent to it looks up the values in the pivot table to pull out the relevant data for printing. Whenever the worksheet is activated it will run a code that will update the pivot table, and then filter out all of the extra blank rows I included in the table to make sure if the pivot table grew then the table would still pick up the extra data. It will then update the number of rows to print on the top of each page as well, but I haven't finished that code yet.

Private Sub Worksheet_Activate()

Dim lo As ListObject
Dim iCol As Long
Dim SelectedCell As Range
Dim TableName As String

Dim FirstGuideRow As Long
Dim LastGuideRow As Long

ThisWorkbook.RefreshAll

Cells(5, 7).Select
Set SelectedCell = ActiveCell

On Error GoTo NoTableSelected
TableName = SelectedCell.ListObject.Name
Set lo = ActiveSheet.ListObjects(TableName)

iCol = lo.ListColumns(1).Index

lo.Range.AutoFilter Field:=iCol, Criteria1:=” <> ”

FirstGuideRow = Range("A:A").Find("*_*").Row - 1
LastGuideRow = Application.WorksheetFunction.CountIf(Range("A:A"), "*_*") + Range("A:A").Find("*_*").Row - 1


Cells(1, 1).Select

Exit Sub
NoTableSelected:
  MsgBox "There is no Table currently selected!", vbCritical

End Sub

 

[Xipha]

This is a partial screenshot of what the table looks like

 

[Fluff]

The criteria looks wrong on this line

lo.Range.AutoFilter Field:=iCol, Criteria1:=” <> ”

The quotes are of the wrong type, it should be "<>"

 

[Xipha]

Whenever I change it to that it changes it back

 

[Xipha]

So I randomly put a space after the equal sign and then pasted "<>" from your message above and suddenly it worked. I'm glad I spent 2 days trying to figure that out ?‍

 

[Fluff]

You're welcome & thanks for the feedback