I have managed to achieve more or less what i wanted, which is to copy paste a set of rows and delete blanks from examples i found here and on the web.
But the problem now is after i have copied and paste the rows, i noticed that the first row is always deleted and after i paste another set of rows, the first row gets deleted again.
Here is the code. Since i merely copied the codes, i dont fully understand what they do thus i am unable to find the mistake. Hope you guys can help me here.
Private Sub CommandButton2_Click() ' Copy Rows Worksheets("SheetJS").Range("C43:C543").Copy ' Open Database Sheet Workbooks.Open Filename:="blahblah\output.xlsx" ' Find Last Row lastrow = Sheets("Main").Cells(Rows.Count, "A").End(xlUp).Row Application.ScreenUpdating = False ' Paste Rows Sheets("Main").Range("A" & lastrow + 1).PasteSpecial (xlPasteValuesAndNumberFormats) With Worksheets("Main") .AutoFilterMode = False .Range("A1:A" & lastrow).AutoFilter field:=1, Criteria1:="" .Range("A1:A" & lastrow).SpecialCells(xlCellTypeVisible).EntireRow.Delete .AutoFilterMode = False End With Application.CutCopyMode = False Application.ScreenUpdating = True ' Close Database 'Workbooks("output.xlsx").Close SaveChanges:=True End Sub