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
Sheets(“sheet2”).Activate
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Sheets(“sheet2”).Range(Cells(erow, 1), Cells(erow, 2))
Sheets(“sheet1”).Activate
End If
Next i
Application.CutCopyMode = False
End Sub
Further study:

One thought on “Copy non blank data from sheet1 to sheet2 with VBA

  1. MANAS KUMAR SARKAR

    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.

    Regards
    Manas
    9437464187

    Reply

Leave a Reply

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