VBA Copy only non blank cells

Question

does anyone one how to instead of static range:

 '~~> Copy the range
    wsI.Range("A1:K50").Copy

To change it to dynamic where A1:A50 is calculated depending on non-blank cells.

So if Sheet1 has 23 lines and the rest of them are blank so range would be: A1:K23?

use `wsI.Range("A1:K50").SpecialCells (xlCellTypeVisible).Copy` — Shai Rado, Feb 20 '17 at 10:58
Duplicate of http://stackoverflow.com/questions/821364/selecting-non-blank-cells-in-excel-with-vba — zmechanic, Feb 20 '17 at 11:00
Thanks for the answer @Shai Rado , but in my A1:K50 cels there are some "" space cells which are still copied ... I only need to copy the ones that has any sort of valus except "" in it — Freedox, Feb 20 '17 at 14:10
`wsI.Range("A1:K50").SpecialCells(xlCellTypeConstants).Copy` — user3598756, Feb 20 '17 at 15:31

score 0 · Answer 1 · answered Feb 22 '17 at 21:39

just in case someone looks for an answer:

issue - when copying data from other sheet where I had formulas, pasted cells are blank, but not actually blank. It holds some sort of 'nothing'...

solution ,SpecialCells(xlCellTypeVisible).Copy to copy data and paste in the same workbook with Paste:=xlPasteValuesAndNumberFormats (it holds date value as this was something I needed). Then used "find and replace" code to clear "blank, but not blank" cells.

'~~> Find "" and replace with pneumonoultramicroscopicsilicovolcanoconiosis

Worksheets("paste").Range("A1:K500").Cells.Replace What:="", Replacement:="pneumonoultramicroscopicsilicovolcanoconiosis", LookAt:= _
    xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

'~~> Find pneumonoultramicroscopicsilicovolcanoconiosis and replace with ""

Worksheets("paste").Range("A1:K500").Cells.Replace What:="pneumonoultramicroscopicsilicovolcanoconiosis", Replacement:="", LookAt:= _
    xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

VBA Copy only non blank cells

1 Answers1