Pasting text on Visible range error

[rex759]

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.

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

 

[Fluff]

How about

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****"
     
   '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 > 1 Then
      .AutoFilter.Range.Offset(1).Columns(6).Resize(Xrow - 1).Value = "HDP"
   End If
End With

 

[rex759]

Thank you! Works perfectly

 

[Fluff]

You're welcome & thanks for the feedback.

For future reference when Marking as solution, you need to select the post that helped the most, not your post saying it works. I have changed it for you this time.

 

[rex759]

ok, makes sense, wasnt sure.

 

[Fluff]