Paste Set of Data onto last row

[Nick70]

Hi,

I have some code that takes some data in Sheet2 and copies it in Sheet1 on the row after the last one see below:

Sub CopyAdditionaData()

'Copies data into Sheet1 from Sheet2

Dim Lr As Long, lc As Integer
Dim wb As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
Dim lastRow As Long

Set wb = ThisWorkbook
Set ws1 = wb.Worksheets("Sheet2")
Set ws2 = wb.Worksheets("Sheet1")

ws1.Activate
Lr = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
lc = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
Range("A3", Cells(Lr, lc)).Copy Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

End Sub

The problem I have is that there is a filter in Sheet1 so the last row is not the last row with data but the last VISIBLE row.
How can I change the code so that the data is pasted from Sheet2 to Sheet1 on the first empty row (so the one after the last row with data so ignoring filter)?

As a further example if I have data in Range(A2:C10) the last row with data will be row Range(A10:C10) but if I have a filter that excludes Range(A10:C10) then my last VISIBLE row with data will be Range(A9:C9) according to VBA code.
We should therefore remove filter from Sheet1, then the count rows, then apply filter again and copy data from Sheet2 to Sheet1.

Let me know in case not clear what I am trying to do.

Thanks,
N.

 

[dsehguht]

adding this simple line should clear any filters for your active ws1 sheet.
then just reapply the filter.

ws1.ShowAllData

or

you could just check to see if the next row is hidden.

Do While Rows(Lr + 1).Hidden = True
    Lr = Lr + 1
Loop

 

[Nick70]

great thanks!
N.