Odd Error When Copying Filtered Data

[reberryjr]

I have the following snippet of code that is giving me trouble. Essentially, I'm filtering for the word "Keep" earlier in the code. That works. The below snippet identifies sDLR and mNLR + 1 correctly. However, if there is only 1 record with "Keep" and that record is in row 2, the code is trying to copy all of rows 1 & 2. In trying to figure out the issue, I found that if I move the record with "Keep" to row 5, the code runs as expected.

With sD.Range("B2:B" & sDLR).SpecialCells(xlCellTypeVisible).Copy
    mN.Range("H" & mNLR + 1).PasteSpecial (xlPasteValues)
End With

Thoughts?

 

[RoryA]

If sDLR is 2, then you are applying specialcells to one cell only, which has the effect of applying it to the used range of the worksheet. You'll want to test for that case specifically.

 

[reberryjr]

If sDLR is 2, then you are applying specialcells to one cell only, which has the effect of applying it to the used range of the worksheet. You'll want to test for that case specifically.

@RoryA I was having the same issue with a different snippet. This appears to work, but I'm curious if there's a cleaner/more efficient way to go about it.

If mNLR = mN.Range("C2").Row Then
    mN.Range("N2").Value = "N/A"
Else
    mN.Range("N2:N" & mNLR).SpecialCells(xlCellTypeVisible).Value = "N/A"
End If