Autofilter no return

[OX_2005]

I cant figure out what I am doing wrong here. The If part of my Macro is coming back with an Error and I am not sure why. I am trying to get it to go to end if the Auto Filter returns nothing.

lr3 = ThisWorkbook.Worksheets("Scheduled Worksheet").Cells(Rows.Count, 1).End(xlUp).Row
    sh1.Range("A1:AM" & lr3).AutoFilter Field:=35, Criteria1:="N"
    If sh1.Range("A2:AM" & lr3).SpecialCells(xlCellTypeVisible).Rows.Count > 0 Then
    Application.DisplayAlerts = False
    sh1.Range("A2:AM" & lr3).SpecialCells(xlCellTypeVisible).delete
    End If

 

[OX_2005]

I have figured out what I was doing wrong

 

[Akuini]

I have figured out what I was doing wrong

Could you tell us?
AFAIK, using rows.count isn't reliable on non-contiguous range.

 

[OX_2005]

Could you tell us?
AFAIK, using rows.count isn't reliable on non-contiguous range.

So what I had wrong was in the IF statement I had sh1.Range("A2:AM"&lr3). It should have been sh1.Range("A1AM"&lr3). I had it starting at the wrong row level, and then I also set it to >1 instead of >0.

After I did that it started working.

 

[Akuini]

Can you please try this (on your revised code):
Filter your data to make row 2 hidden.
I suspect the rows.count is 1, even if there is visible row below it.

 

[OX_2005]

Can you please try this (on your revised code):
Filter your data to make row 2 hidden.
I suspect the rows.count is 1, even if there is visible row below it.

Yes the row count is coming back 1 because after the code auto filtered field 35 by "N" it found that there were none so I was needing it to skip the delete part of the code that kept throwing the error and move on to the part after End If. So it is counting the header row as 1 which is why i put Greater then 1

 

[Akuini]

Yes the row count is coming back 1 because after the code auto filtered field 35 by "N" it found that there were none

What i meant was, try to filter your data where there are result (filtered data) but the row 2 is filtered out (hidden), in this case you want to delete the filtered row, right?
But if the rows.count is 1 then it will skip the the deleting part.
Try this example:

Book3
	A	B	C
1	aa	bb	cc
3	x	Dustin	Zaiden
5	x	Cory	Leonardo
6	x	Tate	Iker
7
8
Sheet2

Sub try1()
Debug.Print Range("A1:A6").SpecialCells(xlCellTypeVisible).Rows.Count  'result = 1,  incorrect result
End Sub

You can see that row 2 is filtered out (hidden) but there are 3 visible rows below it. But the rows.count is 1 instead of 4 as you might expected.

Counting cells.count (in column 1 only) is more reliable, like this:

Sub try2()
Debug.Print Range("A1:A6").SpecialCells(xlCellTypeVisible).Cells.Count 'result = 4, correct result
End Sub

 

[OX_2005]

What i meant was, try to filter your data where there are result (filtered data) but the row 2 is filtered out (hidden), in this case you want to delete the filtered row, right?
But if the rows.count is 1 then it will skip the the deleting part.
Try this example:

Book3
	A	B	C
1	aa	bb	cc
3	x	Dustin	Zaiden
5	x	Cory	Leonardo
6	x	Tate	Iker
7
8
Sheet2

Sub try1()
Debug.Print Range("A1:A6").SpecialCells(xlCellTypeVisible).Rows.Count  'result = 1,  incorrect result
End Sub

You can see that row 2 is filtered out (hidden) but there are 3 visible rows below it. But the rows.count is 1 instead of 4 as you might expected.

Counting cells.count (in column 1 only) is more reliable, like this:

Sub try2()
Debug.Print Range("A1:A6").SpecialCells(xlCellTypeVisible).Cells.Count 'result = 4, correct result
End Sub

Let me show a little more of my code. So the first auto filter is if column AI shows and "N" and the second Auto filter shows if there are any "" (Blanks) You will see it shows there are blanks in the column and after the second auto filter runs all the blanks are deleted. The code is written the same for both auto filters just the criteria is different. I hope this answers what you were asking me. LOL. I am still new to VBA and the Message board. Sorry you are having to deal with a newbie. Also nothing is hidden until the Macro runs.

sh1.AutoFilterMode = False
lr3 = ThisWorkbook.Worksheets("Scheduled Worksheet").Cells(Rows.Count, 1).End(xlUp).Row
    sh1.Range("A1:AM" & lr3).AutoFilter Field:=35, Criteria1:="N"
    If sh1.Range("A1:AM" & lr3).SpecialCells(xlCellTypeVisible).Rows.Count > 1 Then
    Application.DisplayAlerts = False
    sh1.Range("A2:AM" & lr3).SpecialCells(xlCellTypeVisible).Delete
    End If
sh1.AutoFilterMode = False
lr3 = ThisWorkbook.Worksheets("Scheduled Worksheet").Cells(Rows.Count, 1).End(xlUp).Row
    sh1.Range("A1:AM" & lr3).AutoFilter Field:=35, Criteria1:=""
    If sh1.Range("A1:AM" & lr3).SpecialCells(xlCellTypeVisible).Count > 1 Then
    Application.DisplayAlerts = False
    sh1.Range("A2:AM" & lr3).SpecialCells(xlCellTypeVisible).Delete
    End If
sh1.AutoFilterMode = False