Excel 2013 unable to detect blank cells

[MarkB1]

Hello,

I am wishing to create code to jump down to the first empty row of Column A. However, for some reason, Excel 2013 is not treating these blank rows as actually blank. This is evident by using the Go To Special (Blanks) function, by testing for blanks in Column A using formulas (e.g., =CODE(A3), etc.), and other methods.

I'm wishing to take the contents of cell B2 and paste it within the first empty row of column A. The code I'm using is below:

    Range("B2").Select
    Selection.Cut
    Range("A1").Select
    Application.Goto Cells(Rows.Count, "A").End(xlUp).Offset(1), Scroll:=True
    ActiveSheet.Paste

Unfortunately, this does not paste within the proper cell (in this case, Cell A3). Any ideas?

https://www.dropbox.com/s/r93a697wbvqc1ye/Test Workbook2.xlsx?dl=0

Thanks!

 

[Snakehips]

MarkB1,

Your cells in A do indeed appear to be empty and a formula test with e.g. =LEN(A3) returns 0.

However, there is something invisibly amiss.

In the immediate pane of the vba editor, type .....

Range("A3:A500").Clear

and then hit return and you should find that will clear it.

 

[MARK858]

Selecting the column and using Data - Text to columns then clicking finish also worked for me, then I used the code...

Sub xxx()
    Range("B2").Cut Cells(Rows.Count, "A").End(xlUp).Offset(1)
   
End Sub

 

[MarkB1]

Selecting the column and using Data - Text to columns then clicking finish also worked for me, then I used the code...

Sub xxx()
    Range("B2").Cut Cells(Rows.Count, "A").End(xlUp).Offset(1)
   
End Sub

Thanks for the help, guys. Both solutions worked--with Mark's solution being the best-fitting for my overall code. I do have a question though--what is going on and why does the Text-to-Columns actually work?

 

[MARK858]

what is going on

I am as unsure exactly what was actually in the cell as Snakehips appeared to be with the statement

However, there is something invisibly amiss

as I couldn't extract a specific character number from the cell but using an =ISBLANK()
formula always came up FALSE so there was definitely something there.

why does the Text-to-Columns actually work?

Exactly how the mechanics actually work I can't tell you as I don't know enough about Microsoft's programming, but basically if you go to text to columns and click the next button twice on the screen that then appears towards the top you will see there is a section which changes the data format (rather than the normal formats which only changes the appearance and not the underlying value/text etc. of the cell).

It is this that we are using as it seems to strip out most annoying characters (you will see it get used quite often to change numbers stored as text to real numbers).

Afraid not my most technical answer ever but hopefully it helps a tiny bit.