Empty Dynamic cells not empty

[Yoepy]

I have a series of Dynamic cells with

If(something="","",do something else).

My problem is that counta or xldown etc, see the empty cells as having data.
Is there a way to have them seen as being empty. I do want to cut paste elsewhere if that helps.

Thanks.

 

[Joe4]

COUNTA will count those cells.
If those cells are returning numbers (in the "do something else" part), use COUNT instead.
Otherwise, if they are returning text, try SUMPRODUCT instead, i.e.

=SUMPRODUCT(--(LEN(range)>0))

See: https://exceljet.net/formula/count-cells-that-are-not-blank

 

[Yoepy]

If I cut and paste to another column, can I not have the paste exclude the blank cells? I'm currently using XLdown, but it goes all the to the bottom of the blank cells too.

 

[Joe4]

Cells that have formulas are NOT considered empty (even if they are set to return the empty string). So xlDown will NOT do what you want.
Are all these "empty" cells are the bottom of the data, or are they mixed up within the column?

 

[Yoepy]

Yes, they are at the bottom. Even when I cut/paste/special value, the new location blanks are still not empty.

 

[Joe4]

Even when I cut/paste/special value, the new location blanks are still not empty.

Yes, the empty string and NULL are not the same.

Are you looking to do this copy automatically using VBA?
Where are you copying from/to?

 

[Yoepy]

Yes, I am using VBA and copying to another page in the same workbook.

 

[Joe4]

I found how you can locate the last cell in a column that returns a value here: https://exceljet.net/formula/get-value-of-last-non-empty-cell
(specifically, the part listed "Position of the last value"), but haven't been able to convert it to VBA yet.

But I think there may be a better way of going about this. Since you are already using VBA anyway, can't you just program it to copy down your original IF formula exactly the number of rows that you need, so you don't copy it down any extra rows. Then your xlDown method would work fine.

 

[Fluff]

To make the "blank" cells genuinely blank you can use

 Range("B:B").Value = Range("B:B").Value

changing the range to suit