Dont paste blank cells.

  • Im using this line of code in some VBA to copy rows with data in them:

    lngLastRow = Sheets("RISKS").Range("A65535").End(xlUp).Row

    The problem is that these cells, although blank, contain formulas. How can I stop these being copied?

    Thanks in advance,


  • thanks for the reply, I dont believe I can do that.

    so its this line:

    With objWord.ActiveDocument.Bookmarks("RISKS").Range.Characters.Last.Next.PasteAppendTable

    I can look at other paste options but this is pasting into a table and other options have formatting issues. What do you think?



    If you don't think there's a "one line" solution here I could add code that copies this data to a new sheet first and then carries on with the rest of the code.

  • Using the below code, i still get a page full of zeros instead of blanks.

    Is there anything obviously wrong there?

    Thanks, Andy

  • Do these formulas evaluate to an empty string("") or to zero but your sheet (Options, Advanced, Display options for this worksheet) or cell number format is set to not display zeros? Using xlPasteValues with formulas that evaluate to "" leaves the copied formula cells empty.

  • Dave - I'm unsure but unwilling to test that at the moment (It's taken weeks to get the paste into a word table working as I want). I could try this approach to put the rows I want onto a new sheet though.

    Johnathan - you are correct I had turned off show zeros. I have put them back on now.

    FYI: this is the formula in each cell in the table

    =INDEX('RISKS Import'!$A$1:'RISKS Import'!$H$77,K16,$N$2)

    Thanks, Andy

  • So are you now okay with the zeros in your table in Word? If not, you could make your formula do blanks, e.g.,

    =IF(INDEX('RISKS Import'!$A$1:'RISKS Import'!$H$77,K16,$N$2)=0,"",INDEX('RISKS Import'!$A$1:'RISKS Import'!$H$77,K16,$N$2))
  • I havn't tested this but it seems thats what im looking for, It will take me a while to get all the references correct. I'll do that tomorrow.

    i did get it working using this:

    ActiveSheet.Range("A4" & ":H65").Select
        For Each Cell In Selection
            If Cell.Value = 0 Then
                Cell.Value = ""
            End If

    But it was very slow at deleting a page of 0's.

    Really appreciate your help. This was the final stage in a long project of mine. Happy to be near completion!

    Cheers, Andy.

  • That will overwrite your formulas, so I doubt that you actually want to do that. If so, this will be faster:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!