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

 

[Jon von der Heyden]

I *think* I understand the problem.

What happens if you try this:

With Range("A1").End(xlDown).Offset(1, 0)
    .PasteSpecial xlValues
    .Value = .Value
End With

 

[Dani_LobP]

Thanks for the reply, i tried modifying this part of code but still would paste empty cells in the rows. Formulas won't appear since it pastes values, but it would "paste" empty cells that actually are accounted later as if they had something inside.

No luck i guess.

I *think* I understand the problem.

What happens if you try this:

With Range("A1").End(xlDown).Offset(1, 0)
    .PasteSpecial xlValues
    .Value = .Value
End With

 

[MARK858]

Does the below help any?

Dim LastRow As Long
LastRow = Columns("A").Find("*", , xlValues, , xlByRows, xlPrevious).Row
Range("A29:CI" & LastRow).Copy

 

[Jon von der Heyden]

Thanks for the reply, i tried modifying this part of code but still would paste empty cells in the rows. Formulas won't appear since it pastes values, but it would "paste" empty cells that actually are accounted later as if they had something inside.

No luck i guess.

Did you try this? Because whilst it will still copy the blank rows, the .Value = .Value component will convert those to true blanks, thus allowing your LastRow calculation to go to the last non-blank, thus reporting the correct last row position.

 

[Dani_LobP]

Did you try this? Because whilst it will still copy the blank rows, the .Value = .Value component will convert those to true blanks, thus allowing your LastRow calculation to go to the last non-blank, thus reporting the correct last row position.

yes, i tried. when pasting in the new sheet. it would still grab empty rows when doing ctrl+shift down for example. Not sure thou 100% what changes with your code, but still happens same outcome.

 

[Jon von der Heyden]

Ok, well first I think you need to clear the non-blank blank rows from your destination sheet (as a once off), and then try:

Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlValues
Selection.Value = Selection.Value

I had an error in my prior code, it was not applying the value conversion to the pasted range.

 

[FatBoyClam]

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

 

[Dani_LobP]

Does the below help any?

Dim LastRow As Long
LastRow = Columns("A").Find("*", , xlValues, , xlByRows, xlPrevious).Row
Range("A29:CI" & LastRow).Copy

Nice! thanks! this one seems to work , togehter with @Jon von der Heyden code, just one thing.
I changed a bit LastRow since as i said, table where issue happens starts in Row 28 (headers) with data appering starting row 29.

Dim LastRow As Long
LastRow = Range("A28").Find("*", , xlValues, , xlByRows, xlPrevious).Row
Range("A29:CI" & LastRow).Copy

This actually copies the header also, since for some reason it was not doing it before.
What can i change so it just copies from row 29 to below without header in row 28?

Thanks in advance!

 

[Dani_LobP]

Ok, well first I think you need to clear the non-blank blank rows from your destination sheet (as a once off), and then try:

Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlValues
Selection.Value = Selection.Value

I had an error in my prior code, it was not applying the value conversion to the pasted range.

ok this one works wonders! thanks!