{"id":7755,"date":"2017-06-13T05:36:54","date_gmt":"2017-06-13T05:36:54","guid":{"rendered":"http:\/\/www.nurturetechacademy.in\/?p=7755"},"modified":"2017-06-10T15:05:49","modified_gmt":"2017-06-10T15:05:49","slug":"pasting-data-filtered-rows","status":"publish","type":"post","link":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/pasting-data-filtered-rows\/","title":{"rendered":"Pasting Data on Filtered Rows"},"content":{"rendered":"

When we paste data on filtered rows it always past it on both visible and hidden rows. And if you are doing this for a huge set of data then I am sure you must be pasting it manually and wasting lot of time and efforts. Don\u2019t worry guys, we have a solution for this but only through VBA.<\/p>\n

So let\u2019s see an example, below is a screenshot of a filtered data:<\/p>\n

 <\/p>\n

\"1\"<\/p>\n

 <\/p>\n

When you simply use Copy and paste it looks like this:<\/p>\n

 <\/p>\n

\"2\"<\/p>\n

 <\/p>\n

So it will get pasted on hidden rows also, to avoid the same, please follow the below steps :<\/p>\n

    \n
  1. Press Alt + F11 keys simultaneously, and a Microsoft Visual Basic for Applications (VBA) window will open.<\/li>\n
  2. Click Insert > Module, then paste below VBA code to the Module window.<\/li>\n<\/ol>\n

    Sub PasteOnVisibleCells()<\/em><\/strong><\/p>\n

    \u00a0\u00a0\u00a0 Dim rg1 As Range<\/em><\/strong><\/p>\n

    \u00a0\u00a0\u00a0 Dim rg2 As Range<\/em><\/strong><\/p>\n

    \u00a0\u00a0\u00a0 Dim CopyRg As Range<\/em><\/strong><\/p>\n

    \u00a0\u00a0\u00a0 Dim PasteRg As Range<\/em><\/strong><\/p>\n

    \u00a0\u00a0\u00a0 Set CopyRg = Application.Selection<\/em><\/strong><\/p>\n

    \u00a0\u00a0\u00a0 Set CopyRg = Application.InputBox(“Copy Range :”, , CopyRg.Address, Type:=8)<\/em><\/strong><\/p>\n

    \u00a0\u00a0\u00a0 Set PasteRg = Application.InputBox(“Paste Range:”, , Type:=8)<\/em><\/strong><\/p>\n

    \u00a0\u00a0\u00a0 For Each rg1 In CopyRg<\/em><\/strong><\/p>\n

    \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 rg1.Copy<\/em><\/strong><\/p>\n

    \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 For Each rg2 In PasteRg<\/em><\/strong><\/p>\n

    \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 If rg2.EntireRow.RowHeight > 0 Then<\/em><\/strong><\/p>\n

    \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 rg2.PasteSpecial<\/em><\/strong><\/p>\n

    \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Set PasteRg = rg2.Offset(1).Resize(PasteRg.Rows.Count)<\/em><\/strong><\/p>\n

    \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Exit For<\/em><\/strong><\/p>\n

    \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End If<\/em><\/strong><\/p>\n

    \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Next<\/em><\/strong><\/p>\n

    \u00a0\u00a0\u00a0 Next<\/em><\/strong><\/p>\n

    \u00a0\u00a0\u00a0 Application.CutCopyMode = False<\/em><\/strong><\/p>\n

    End Sub<\/em><\/strong><\/p>\n

      \n
    1. Click F5 key or the Run button, a dialog box will appear for you to select Range to copy<\/li>\n<\/ol>\n

       <\/p>\n

      \"3\"<\/p>\n

       <\/p>\n

      4. After selecting the range, click OK, and then another dialog box will appear for you to select a range to paste the copied data in.<\/p>\n

       <\/p>\n

      \"4\"<\/p>\n

       <\/p>\n

      5. Click OK to get your desired result.<\/p>\n

       <\/p>\n

      Download the same file here<\/a>.<\/p>\n

      <\/div>\n

      <\/path><\/svg><\/i> \"Loading\"<\/p>\n

      <\/div>\n","protected":false},"excerpt":{"rendered":"

      When we paste data on filtered rows it always past it on both visible and hidden rows. And if you are doing this for a huge set of data then I am sure you must be pasting it manually and <\/p>\n

      <\/div>\n

      <\/path><\/svg><\/i> \"Loading\"<\/p>\n

      <\/div>\n","protected":false},"author":1,"featured_media":7760,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"footnotes":""},"categories":[56],"tags":[71,57,233,235,234],"class_list":["post-7755","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel","tag-advanced-excel","tag-excel","tag-filter","tag-filtered-data","tag-vba"],"_links":{"self":[{"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/posts\/7755","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/comments?post=7755"}],"version-history":[{"count":1,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/posts\/7755\/revisions"}],"predecessor-version":[{"id":7761,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/posts\/7755\/revisions\/7761"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/media\/7760"}],"wp:attachment":[{"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/media?parent=7755"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/categories?post=7755"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/tags?post=7755"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}