March 12, 2016

Copy non blank data from sheet1 to sheet2 with VBA

How to copy non blank data from sheet1 to sheet2 with VBA. Sometimes we may have incomplete data in our Excel worksheet. Now we would like to create a separate worksheet with only the complete data. With VBA we can copy the completed data from one worksheet to another quickly and easily. Watch the training video below:

You can view this video on YouTube also.

Here’s the complete VBA code to quickly copy the completed data:

Sub copyNonBlankData()
Dim erow As Long, lastrow As Long, i As Long
lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
If Sheet1.Cells(i, 1) <> “” Then
Sheets(“Sheet1”).Range(Cells(i, 1), Cells(i, 2)).Copy
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Sheets(“sheet2”).Range(Cells(erow, 1), Cells(erow, 2))
End If
Next i
Application.CutCopyMode = False
End Sub
Further study:

2 thoughts on “Copy non blank data from sheet1 to sheet2 with VBA

  1. Sir,

    I am a great fan of yours and I had seen all the videos you had published in your website/youtube on VBA .

    I writing this mail to guide me to resolve my problem which I am facing while copying some data from one file (ZBLMASTER_SAP_2) to other (ZAM3N_NEW_SAP_2) using
    INDEX & MATCH combination.

    When I using the under mentioned formula in VBA then it copies data as desired but I have to indicate the last row number in integer form (i.e. 2072).

    ActiveCell.FormulaR1C1 = “=INDEX(ZBLMASTER_SAP_2.xlsx!Format, MATCH(RC[-9],[ZBLMASTER_SAP_2.xlsx]Format!R2C2:R2072C2,0),8)”

    However, the formula is not working when I am using a variable instead of integer i.e. lastrow.

    ActiveCell.FormulaR1C1 = “=INDEX(ZBLMASTER_SAP_2.xlsx!Format, MATCH(RC[-9],[ZBLMASTER_SAP_2.xlsx]Format!R2C2:RlastrowC2,0),8)”

    Where lastrow = lastrow_ZBLMASTER_SAP_2 = Sheets(“Format”).Range(“B” & Rows.Count).End(xlUp).Row

    Kindly, help.


  2. Dear sir,
    Thank you so much for your videos it helped me a lot in my work(Brad and Butter). Sir, I would like to thank you with some little gift what I can. Sir, I have some task to do in my work. If you could share your email ID I would send you my task. It would be a great help.

    Kindly, help.


Comments are closed.