How to Apply paste special for multiple selection using Macro(When inbetween Rows are Hidden)

[Akash kothari]

Hello,

I am facing issue while applying paste special(Alt+E+S+V) after using visible cells only(Alt+; ) for the column that contains the V-lookup . Error Msg thats coming is "That command Cannot be used ob multiple criteria.

There are few Rows that are hidden in between.
Is there is Any way to apply paste special on visible cell only using Macro.

Thank you
Akash

 

[NARAYANK991]

Hi Akash ,

PasteSpecial is a command in Excel , or a function in VBA , that has a certain functionality.

To ask whether there is a way to reproduce this functionality using VBA is not the right way to go , even though the answer is YES. Specify what you wish to do , and a VBA routine to do that can be developed.

Narayan

 

[Abhijeet R. Joshi]

Hi Akash,

Just my piece of advice..
As you have applied the formulas to some cells after filtering it and you have selected only the visible rows and dragged the formulas down so you can remove the filter, select the whole column then paste special the same and filter only the ones that have the values in it.
(Since you have used the visible rows only, the formulas impacts only the selected rows hence even when you remove the filter it will replace the formulas with values for those which have a formula and the remaining will be blank)..

Hope this helps...