blank cells for specific row only

Hi all,

Have read the forums and understand that there is a piece of code that works very well for looping and stopping at a blank cell as follows:-

MyLastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row

However I have two columns, one with a list of items and a second column next to it that has a formula that will pick up a vlookup reference, if it cannot find it then it leaves a blank cell ("").

This is a formula that is copied down all rows where it is possible to enter items.

Perhaps an example explains it better:-

Column1..........Column2
123................=if(isna(vlookup(r2c1,range,2,0)),"",vlookup(r2c1,range,2,0))
234................=if(isna(vlookup(r3c1,range,2,0)),"",vlookup(r2c1,range,2,0))
blank..............=if(isna(vlookup(r4c1,range,2,0)),"",vlookup(r2c1,range,2,0))
....................etc

(sorry spaces not showing hence the ...)

Now the problem is the MyLastRow appears to look in column 2 for the last cell before a blank and not column 1. As Column 1 is actually being used to pick up an image it fails once it hits a blank.

Hope that makes sense. I want the last row return to be defined from column 1 only.

Thanks.

You are mistaken what the find command does.

It finds the last cell that has an entry on it on the activesheet and supplies the row number of that cell to the MyLastRow variable. It does not find blank cells.

Here are some different methods to find rows & columns

In all cases you will need to add 1 to the result to get the blank cells row or column number

'find row before next blank cell in column A
iLastRow = Range("A1").End(xlDown).Row

'find last used row in column A
iLastRow = Cells(Rows.Count, "a").End(xlUp).Row

'find last used row on sheet
iLastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row


iLastColumn = Range("A1").End(xlToRight).Column

iLastColumn = Cells(Columns.Count, "a").End(xlToLeft).Column

'find last used column on sheet
iLastColumn = Cells.Find(What:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column

Please read posting rules - see red link below