COUNTA Counting "Blank" Cells

[surkdidat]

I have a spreadsheet covering A to THG, and 1000+ rows deep.

WHen I use COUNTA on a row, its counting "blank" cells. All cells (when it has been exported" have come as "TEXT".

It will take a long time (and have another sheet about 20x as big with the same issue.

TEXT TO COLUMNS works but will take an age going through each column individually.

Is there either a macro I can have do this (starting column M through to THG?

(Have also use Replace "" with "+++++" but again, this solution will take an absolute age.

If I use

=CODE(THG235)

for example it comes up as #VALUE!

I am guessing a macro that could run doing the Text To Columns is the best solution? If so, ohw would I go about this.

Welcome to other ideas!

 

[Fluff]

Maybe

Sub surkdidat()
   With Range("M2:THG" & Range("A" & Rows.Count))
      .Value = .Value
   End With
End Sub

 

[surkdidat]

Maybe

Sub surkdidat()
   With Range("M2:THG" & Range("A" & Rows.Count))
      .Value = .Value
   End With
End Sub

Comes up with Error 400. i have tried to add a row number after THG, but even if I do this, is still counting "blank" cells. When I go into the cell, there is no obvious space or character in them

 

[Fluff]

Is there any particular column that will always have data on the last row?

 

[surkdidat]

Is there any particular column that will always have data on the last row?

No, although I could add an erroneous column to add values in all 1000-odd cells if it helps

 

[Fluff]

Ok, how about

Sub surkdidat()
   Dim UsdRws As Long
   
   UsdRws = Cells.Find("*", , , , xlByRows, xlPrevious, , , False).Row
   With Range("M2:THG" & UsdRws)
      .Value = .Value
   End With
End Sub

 

[surkdidat]

Ok, how about

Sub surkdidat()
   Dim UsdRws As Long
  
   UsdRws = Cells.Find("*", , , , xlByRows, xlPrevious, , , False).Row
   With Range("M2:THG" & UsdRws)
      .Value = .Value
   End With
End Sub

Thanks Fluff! All seems to work on the smaller sheet! Again, you have been very helpful

 

[Fluff]

You're welcome & thanks for the feedback.