CountA in pasted data gives not expected result

[wpryan]

I have some data that I want to copy from one worksheet to another. All of the cells have calculations in them. I select them, hit "copy" and then in the destination, hit "paste special" (values). I want to run some code to delete the data in rows where cells B:F are empty. However, when I test the CountA function in the worksheet level, I get an unexpected value... Even though there is nothing visible in the cells, it gives me a return of 5, so it seems that it is considering the formulas as values, even if they are not visibile in the cells. I'm curious where I'm going wrong...

 

[ExceLoki]

can you share what your getting so we can see and troubleshoot a lil easier?
if you can, try using XL2BB plugin

 

[wpryan]

can you share what your getting so we can see and troubleshoot a lil easier?
if you can, try using XL2BB plugin

 

[wpryan]

Thanks for your reply. Here it is:

Rot Pos [°]	Park	OS	OD	OS_Other	OD_Other	CountA?
0​						​
1​
2​
3​
4​	995​
5​
6​
7​
8​
9​
10​
11​
12​
13​
14​
15​				1005.5​	998.5​
16​			996.5​
17​
18​		994​

 

[wpryan]

Thanks for your reply. Here it is:

Rot Pos [°]	Park	OS	OD	OS_Other	OD_Other	CountA?
0​						​
1​
2​
3​
4​	995​
5​
6​
7​
8​
9​
10​
11​
12​
13​
14​
15​				1005.5​	998.5​
16​			996.5​
17​
18​		994​

...I think I deleted the formula by mistake... I'm not accustomed to using XL2BB... I expect in Column G to have the forumla

=COUNTA(B2:F2)

 

[RoryA]

If you copy and paste special - Values a formula that returned "", the resulting cell will not be considered truly blank. Given that it looks like you want to count numbers, you could simply use COUNT rather than COUNTA.

 

[wpryan]

If you copy and paste special - Values a formula that returned "", the resulting cell will not be considered truly blank. Given that it looks like you want to count numbers, you could simply use COUNT rather than COUNTA.

Thanks for your quick answer... I was hoping to delete the rows which don't have any values in range B:F of the row in question, so I could then graph the remaining data. I tried to use the line

tRange.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

, where tRange is Range B2:F722, but I'm getting no luck out of it...

 

[RoryA]

As I said, they are not considered blank. If you use COUNT it should return 0. You can then filter on that column and delete the visible rows.

 

[Fluff]

Another possible option if tRange does not contain any formulae

tRange.Value=tRange.Value
tRange.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

 

[wpryan]

Thanks all for your suggestions. In the end I looped through the range, with this code:

    For Each cell In uRange
        If cell.Value = "" Then
            cell.ClearContents
        ElseIf cell.Value <> "" Then
            cell.Value = cell.Value
        End If
    Next

...that got me past the "countA" problem. I tried Fluff's solution for deleting the rows and got a "Cannot use that command on overlapping cells" message. In the end I did another loop:

    Dim r As Range, rows As Long, i As Long
    Set r = shTemp.Range("B2:F722")
    rows = r.rows.Count
    For i = rows To 1 Step (-1)
    If WorksheetFunction.CountA(r.rows(i)) = 0 Then r.rows(i).EntireRow.Delete
    Next

Probably there's a more elegant solution, but it's working for this weekend hacker...