Paste values in filtered range

[Mr.Karr]

Hello

Can anyone pls provide a code snippet to paste values in the filtered range?
I have a requirement to paste values in a column, can you pls modify below as this pastes values till the unfiltered range.

Range("H17").Copy
Range("H21:H1000").SpecialCells(xlCellTypeVisible).PasteSpecial xlValues
Range("I17").Copy
Range("I21:I1000").SpecialCells(xlCellTypeVisible).PasteSpecial xlValues
Range("K17").Copy
Range("K21:K1000").SpecialCells(xlCellTypeVisible).PasteSpecial xlValues
Range("L17").Copy
Range("L21:L1000").SpecialCells(xlCellTypeVisible).PasteSpecial xlValues
Range("M17").Copy
Range("M21:M1000").SpecialCells(xlCellTypeVisible).PasteSpecial xlValues
Range("N17").Copy
Range("N21:N1000").SpecialCells(xlCellTypeVisible).PasteSpecial xlValues
Range("O17").Copy
Range("O21:O1000").SpecialCells(xlCellTypeVisible).PasteSpecial xlValues
Range("Q17").Copy
Range("Q21:Q1000").SpecialCells(xlCellTypeVisible).PasteSpecial xlValues
Range("AF17").Copy
Range("AF21:AF1000").SpecialCells(xlCellTypeVisible).PasteSpecial xlValues
Range("AK17").Copy
Range("AK21:AK1000").SpecialCells(xlCellTypeVisible).PasteSpecial xlValues
Range("AL17").Copy
Range("AL21:AL1000").SpecialCells(xlCellTypeVisible).PasteSpecial xlValues
Range("AM17").Copy
Range("AM21:AM1000").SpecialCells(xlCellTypeVisible).PasteSpecial xlValues
Range("AN17").Copy
Range("AN21:AN1000").SpecialCells(xlCellTypeVisible).PasteSpecial xlValues

For an example:
I need to copy values from H17 and paste it as values under column H filtered cells only

Range("H17").Copy
Range("H21:H1000").SpecialCells(xlCellTypeVisible).PasteSpecial xlValues

Thanks a lot in advance.

 

[BBD]

Hi,

The trick is to find the last row of the sheet when it is filtered rather than keeping row 1000 as constant in the code. Below is a row function, which you will have to paste in the beginning of the module.

Public Function row() As Double
row = ActiveSheet.Cells.Find(What:="*", After:=Cells(Cells.Rows.Count, Cells.Columns.Count), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
End Function

Note : This will work only if there is no data below your data set. It takes the last row number which has the data in the sheet.

Once you have this, you need to modify your code like :

Range("H17").Copy 
Range("H21:H" & row).SpecialCells(xlCellTypeVisible).PasteSpecial xlvalues

Cheers,
BD