A snippet of VBA code please

[Titian]

I have manipulated and sorted my file so that in Column S, the row that I am interested in printing, contains the word "No".

How do I look down column and return the row number of the first row that does not contain "No".

I have attempted to look UP column S however as it contains other things the bottom up count stops at the end of my data set.

Any help is much appreciated.

 

[BBHRVS]

Hi Titian;

Try (if your column is well sorted) the "columndifferences" function :

sub titian()
Dim cellfin, celldeb, macell As Range
Set celldeb = ActiveSheet.Cells(1, 19) 'if your data begins at row 1
Set cellfin = ActiveSheet.Celldeb.end (xldown) 'or any other way to find out your last cell...
Set macell = ActiveSheet.Range(celldeb, cellfin).ColumnDifferences(celldeb)(1)
msgbox macell.row
end sub

Should help I guess.

 

[Titian]

The program falls over at this line

Set cellfin = ActiveSheet.celldeb.End(xlDown)

with the message "Run time error 438", "Object doesn't support this property or method"

Any ideas?

 

[BBHRVS]

Sorry, too fast...
second instruction line should be only :
Set cellfin = Celldeb.end (xldown)

 

[Titian]

Thanks a lot, that's OK now.

Just been tweaking my print range to use my new last row and it all hangs together nicely.

Thanks again for your help.