Why is this VBA code causing rows to unhide?

Dezine · Jan 21st 2021

Hi Everyone,

For some reason this code is causing all rows to unhide. It does exactly what I need besides that little hiccup. Should I use a different method? Thanks in advance for any help.

Code

Option Explicit
Sub PopulatePartsList()
'Application.ScreenUpdating = False
With Sheets("Incoming").Range("V40:AF417")
.AutoFilter
.AutoFilter Field:=4, Criteria1:="<>"
.Offset(0, 0).Copy
End With
With Sheets("Incoming")
.Cells(.Rows.Count, 1).End(xlUp).Offset(1, 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
With Sheets("Incoming").Range("V40:AF417")
.AutoFilter
End With
'Application.ScreenUpdating = True
End Sub

Display More

gijsmo · Jan 21st 2021

If the rows were being hidden as a result of using one or more filter conditions on the V40:AF417 range then you are completely clearing that filter with the following code:

Code

.AutoFilter

which in turn removes any rows previously hidden by the filter selection

Dezine · Jan 21st 2021

Thanks Gizmo! I understand now why it is happening. I will create a new thread asking for help accomplishing the task.

Share