Find first blank cell that LOOKS empty in a column

[bat18]

I am trying to find the first blank cell in column A, I have used the code

Range("A1").End(xlDown).Offset(1, 0).Select

The problem thatg is occuring is thatg it is finding cell A501 every time, this is becuase I used to have formula all the way down to cell A500 before copying the column and pasting simply the values over it.

I now need to find the first blank cell in this column but it shoots me down to 501 every time, any ideas on how to find the first cell that looks blank??

Cheers

 

[VoG]

Try

Columns(1).Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Offset(1).Select

although you should not need to select that cell in order to work with it.

 

[Jon von der Heyden]

Also, you can force excel not to read those cells as 'occupied' with:

[COLOR="Blue"]On[/COLOR] [COLOR="Blue"]Error[/COLOR] [COLOR="Blue"]Resume[/COLOR] [COLOR="Blue"]Next[/COLOR]
    Columns(1).TextToColumns , DataType:=xlDelimited
[COLOR="Blue"]On[/COLOR] [COLOR="Blue"]Error[/COLOR] [COLOR="Blue"]GoTo[/COLOR] 0

 

[bat18]

Cheers fellas, great help