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:

Published by

Dinesh Kumar Takyar

Welcome to! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: For a structured Excel VBA training course online you can visit:

One thought 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.


Leave a Reply

Your email address will not be published. Required fields are marked *