how to copy past using VBA in excel?

Question

Hellow,
I have a problem with copy past code
I can't identify the last cell in the row "where I would like to past" !!?
Here in the next code, I wrote "Shet.Cells(Rows.Count, "N").End(xlUp).row + 1", and it works well just in case there are no hidden rows, except that last row's value always replace itself !!

So, what should I do to update last row's value every time I execute Sub Copy_Past() ???

 Sub Copy_Past()
    Dim Shet As Worksheet
    Set Shet = ThisWorkbook.Sheets(1)
    Dim LRow As Long
    'To get the latest cell in the column "N", where I would like to paste my data.
    LRow = Shet.Cells(Rows.Count, "N").End(xlUp).row + 1

    'To make a copy form where I selected
    Selection.SpecialCells(xlCellTypeVisible).Copy ActiveSheet.Cells(LRow, "M")
    'To delete the range of data that I selected and after coping them
    Selection.SpecialCells(xlCellTypeVisible).Delete (xlShiftUp)
 End Sub

If you show a sample of your data as well as the result you desire, you will be more likely to get help -- especially if it can be copy/pasted by those who help you. Rather than Sheets(1), I suggest you give the name of the sheet; for example, Sheets("sheetName"). Also, consider using range variables as in this example stackoverflow.com/questions/49564441/… — Tony M

Mohamed Sheshtawy Mohamed Sheshtawy · Accepted Answer · 2018-03-30T01:57:50

I found a solution for this :)

I brought this method from here
https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-msoffice_custom-mso_2007/finding-last-row-including-hidden-rows/af0d7d7c-84f1-44bf-b36a-5abc98a93fa6

Sub xlCellTypeLastCell_Example_Column()
        For LastRow = Columns("N").SpecialCells(xlCellTypeLastCell).row To 1 Step -1
           If Len(Cells(LastRow, "N").Formula) Then Exit For
        Next
        MsgBox LastRow
End Sub

how to copy past using VBA in excel?

1 Answers