VBA thinks blank rows aren't blank?

[bozack]

Hello,

I have small VBA macro that copies a few lines of some variable data and then goes to another spreadsheet, finds the last row of data and then pastes the values into the next row. Since my original dat can be 1-10 rows of data with formulas in each cell, sometimes there's up to 10 rows of data and other times there's only one row of data. The rows without data have formulas such as =If(A2="","",A2) and I am copying this and then special pasting the values into the 2nd sheet. The problem is, even though I'm pasting rows with blank cells, when I later go back and look for the last row of data, it thinks there's data in the blank cells where I just pasted "" or nothing into them. How can i prevent these blank rows from looking like they contain data?


This is the line of code I'm using to find the last blank row:

NextRow = Range("A65536").End(xlUp).Row + 1
Range("A" & NextRow).Select

 

[MrKowz]

Those cells aren't actually "blank". They contain a null character.

One idea would be to find what Excel says is the "last row", then loop backwards to find the last row that does not contain a null character:

Dim NextRow as Long, i as Long
NextRow = Range("A" & Rows.Count).End(xlUp).Row
For i = NextRow to 1 Step -1
    If Range("A" & i).Value <> "" Then 
        NextRow = i + 1
        Exit For
    End If
Next i

 

[Jonmo1]

The problem is that
"" is NOT a Blank. It is a Null Text String.
It technically IS a Value.
Even after paste special / Values.

You can convert those Null Strings to Blanks with something like

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

Hope that helps.

 

[bozack]

Thanks All!! I've sorted it. Yes, they are null characters as opposed to blank. My work around is to filter the cells before copying and filter a specific column that is supposed to have something in it, this eliminates the rows with the null characters. Thanks!!