cells not registering as blank

[tina]

Hi
I was using following code as part of macro which worked fine but now it
errors as no blank cells I tried highlighting row 2 and selecting edit goto
special blank cells states no cells . there are empty cells
Rows("2:2").Select
Selection.SpecialCells(xlBlanks).Select
I have not made changes to spreadsheet layout just imported new data
any Ideas
Thank you
Tina

 

[XP]

Hi Tina,

Sometimes when importing data the imported cells look blank, but really
contain spaces that you cannot see. For example, in a cell, press space bar a
couple times and press enter. The cell looks empty, but it is not. It
contains some spaces.

You may have to resort to a macro to "clean" up your sheet if this is the
case. Look at the "Trim" function in help.

HTH

 

[JLGWhiz]

Hi tina, I get mixed results, but if I use:

Range("B2:H2").SpecialCells(xlBlanks).Select

Then I get the right results for the blank cells.

If I use Rows(2).Select, I get that row selected, but when I
use Rows("2:2").SpecialCells(xlBlanks).Select, it gives me
mixed results. Could be it don't like the Rows("2:2").

 

[tina]

Hi
I tried both methods still not working . I know the cells are blank as the
data imported is my data and i tried coping columns with data to new sheet
i.e. columns a:da then tried still failed to make it even more confusing the
formulas now works in first instance and allows me to enter data in first
cell found but when repeat later to get next cell it fails I can get round
this by range naming cell but I am confused why happening as this macro has
been working fine for a couple of years
Thanks for you time
Tina

 

[JLGWhiz]

This should work.

Sub blnkSpec()
lstCol = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column
Range("A2", Cells(2, lstCol)).SpecialCells(xlCellTypeBlanks).Select
End Sub

It seems that when the Rows collection is used, it somehow tries to
incorporate the used range. I don't know why this occurs, but it did it
several times with me, and sometimes it ignored the specical cells altogether
and did nothing. But the code above worked every time. It sets a variable
range on row 2 that runs from A2 to cell in the last column with data on that
row. I think that is what you really wanted instead of the entire row's
blank cells.