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.

Why is this VBA code causing rows to unhide?

Participate now!

Share