Copy and Paste Rows but not overwrite the hidden rows

Question

I want to say thank you for the help I've been getting lately. So I am testing the VBA and I noticed that when I copy and paste data to the worksheet, it would overwrite the hidden rows. Is there a way to copy and paste the data without overwriting the hidden rows?

Public Sub CNPInStock()    
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim lr1 As Long, lr2 As Long

    Set ws1 = ThisWorkbook.Sheets(Sheets.Count)       'Last Worksheet
    Set ws2 = ThisWorkbook.Sheets(Sheets.Count - 1)   'Second to Last Worksheet

    lr1 = ws1.Range("A" & ws1.Rows.Count).End(xlUp).Offset(1).Row
    lr2 = ws2.Range("A" & ws2.Rows.Count).End(xlUp).Row

    ws2.Range("A1:C" & lr2).Autofilter Field:=3, Criteria1:=">0", Operator:=xlFilterValues

    ws2.Range("A2:C" & lr2).SpecialCells(xlCellTypeVisible).Copy
    ws1.Range("A" & lr1).PasteSpecial xlPasteValues
End Sub

Is the issue with finding the last row that also takes into account hidden rows? If so, [this post](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) has just about every method documented. I believe you will want to use the `Find` method to get the last row that also considers hidden rows. (See the section `Find Last Row in a Sheet` under Siddharth's solution) — urdearboy, May 12 '20 at 16:21

score 0 · Answer 1 · answered May 12 '20 at 19:42

It is the same behavior as with normal copy and paste operation done without VBA.

While you can simultaneously copy data from the multiple rows by Selecting visible cells and then Copying, you cannot Paste into multiple regions with one single operation (if some rows are filtered out, this is basically what you are trying to do).

You need to loop with For ... Next through each row to paste the data.

score 0 · Answer 2 · answered May 12 '20 at 19:58

Maybe the following solution will help you:

Sub CNPInStock()

   'source-sheet
   Dim sws As Worksheet
   'target-sheet
   Dim tws As Worksheet

   Dim i As Long
   Dim j As Long

   Dim iEnd As Long

   Set sws = Sheets("Source")
   Set tws = Sheets("Target")


   'only for faster calculation
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   Application.EnableEvents = False

   'get end of source-sheet
   iEnd = sws.Range("A" & sws.Rows.Count).End(xlUp).Row

   'init row for target-sheet
   j = 1

   'loop the source-sheet
   For i = 1 To iEnd

       'your filter in column 3 with value > 0
       If sws.Cells(i, 3).Value > 0 Then

           'check if row in target-sheet is hidden
           Do Until tws.Rows(j).Hidden = False
               j = j + 1
           Loop

           'copy source to target
           sws.Range(Cells(i, 1), Cells(i, 3)).Copy Destination:=tws.Cells(j, 1)

           j = j + 1
       End If
   Next i


   'back to automatic (slower) calculation
   Application.ScreenUpdating = True
   Application.Calculation = xlCalculationAutomatic
   Application.EnableEvents = True


End Sub

Copy and Paste Rows but not overwrite the hidden rows

2 Answers2