Hello,
The goal of the snippet below is to filter out the rows that do not meet the criteria and add the acronym of “HDP” in column F for the visible rows. If I step through using F8, it works as expected. If run it using F5, is pastes the acronym “HDP” starting with the row past the visible range for another 2000 rows. The workbook has many tabs and the input page is 2000 rows. It seems like its picking up the range on the Input tab. I can add code to clear the contents if needed but not sure why its picking up the range of another sheet.
The goal of the snippet below is to filter out the rows that do not meet the criteria and add the acronym of “HDP” in column F for the visible rows. If I step through using F8, it works as expected. If run it using F5, is pastes the acronym “HDP” starting with the row past the visible range for another 2000 rows. The workbook has many tabs and the input page is 2000 rows. It seems like its picking up the range on the Input tab. I can add code to clear the contents if needed but not sure why its picking up the range of another sheet.
VBA Code:
Dim Xrow As Long, dng As Range
With newSheet
Xrow = Cells(Rows.Count, "A").End(xlUp).Row
'This is the Column to paste "HDP"
Set dng = .Range("F2:F" & Xrow)
'Filter out just stores that start with a 2 or 3
.Range("a1:G1").AutoFilter Field:=3, Criteria1:= _
"=inventory - 2****", Operator:=xlOr, Criteria2:="=inventory - 3****"
If Range("a1:a" & Xrow).SpecialCells(xlCellTypeVisible).Count < 2 Then
GoTo 77
End If
'Filter out store that don’t have cycle count.
.Range("A1:G1" & Xrow).AutoFilter Field:=4, Criteria1:="cycle count audit"
If .Range("a1:a" & Xrow).SpecialCells(xlCellTypeVisible).Count < 2 Then
GoTo 77
End If
'Paste HDP in visbile cells
dng.SpecialCells(xlCellTypeVisible) = "HDP"
End With