Copy/Paste Range issue with VBA when cells are empty (but with formulas inside)

[Dani_LobP]

Hello,

Currently having some issues when copy/pasting some range of rows.
There are some formulas in there that would be populating depending of another table.
So in the end there would be some rows populating from such table, and eventually remaining rows without populated values would remain blank, but, with formulas inside.
When i use vba to copy range, it would actually take those blank cells as cells with values and copy them too, when i only would like to just have rows populated actually..

so this would be first part of the code. Table is pasted in rows A6:A28 (sometimes is just used A6:A8 others more.. thats why i use formulas to populate the rows below A28, because sometimes is longer and others shorter).
A28 would be headers of the small table that autopopulates with formulas that will find values from table above.

LastRow = Range("A28").End(xlDown).Row
Range("A29").Select
Range("A29:CI" & LastRow).Copy

And then when copy this, it would extract it to another sheet/book and paste it. And is where my problem appears, it pastes all rows, populated and ones blanks too.

this part of the code will basically append new rows to the end row of some other table in other file. And it adds blank rows, so next time i do this, it creates a big gap.

Range("A1").End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial xlValues

And this is actually what i have in those cells with formulas i talk about (there are many more columns but as example so it makes sense or easier to understand):
this would be example of row 30 (basically from row 29 till row 40 is same). Row 29 would be populated, and row 30 and so on all empty since there was no more rows in main table to obtain data to populate.
columns below would be A:E

=CONCATENATE(B30,D30,E30)

=IF(OR(P7="",P7=P6),"",P7)

=IF(B30="","",0)

=IF(C30="","",IF(D7="","",LEFT(D7,3)))

=IF(B30="","",0)

Hopefully it makes sense the explanation and someone can help to find a way to fix or change the way i made it work.

Thanks in advance.
Dani

 

[Dani_LobP]

How about this;

Dim r as Range

With Range("A1").End(xlDown).Offset(1, 0)
    .PasteSpecial xlValues
    for each r in .Cells
        If r.Value = "" Then r.ClearContents
    Next

End With

thanks for the help! i tried this too, but wouldn't clear the "empty" cells that are already empty and would still add them in case i for example do ctrl+A to select range or whole table.

Since someone else already managed to find the way i will stay with that line of code and end my query :D thanks again for helping anyways!

 

[Dani_LobP]

If any admin see this, feel free to close it as Solved. Thanks all!

 

[MARK858]

Just as a remark you shouldn't have made the amendment to the Last row code I posted.
The code as was looks from the bottom of Column A up until it finds a value showing. Putting in the Range("28") changes that.

 

[Dani_LobP]

Just as a remark you shouldn't have made the amendment to the Last row code I posted.
The code as was looks from the bottom of Column A up until it finds a value showing. Putting in the Range("28") changes that.

oh true! checked it without amendment and works also good thanks!