try this.....

lrow = worksheet("sheet1").Cells.specialcells(xlcelltypelastcell).row

that will give you the last row  number in the worksheet

I had a few teething problems, and needed to add an 's' to the end of
worksheet...

but it worked a treat - thanks!

*runs off to investigate other specialcell properties :-)

Sub Macro1()
'
' Macro to find the last Row No of a typed column  cells.
' Macro Created on 1/24/2006 by Mactikus
' Assign shortcut key Ctrl+Shift+S
RowNo = Cells.SpecialCells(xlCellTypeLastCell).Row
ColNo = Cells.SpecialCells(xlCellTypeLastCell).Column
For i = RowNo To 1 Step -1
Cells(1, 5).Formula = "=COUNTIF(" & i & ":" & i & ","""")"
If ColNo > Cells(1, 5).Value Then
GoTo BreakNext
End If
Next i
BreakNext:
Cells(1, 6) = i
End Sub

I am using cells A5 to store the COUNTIF function and A6 to store
the last Rown No.

Sorry, my mistake not A5 and A6, shoukd be E1 and F1 instead.

You seem to be creating a sledgehammer to crack a nut here.

Try the following line:

activesheet.cells.specialcells(xllastcell).row

I am sorry you do need a sledgehammer. Try simulate this ~ Do an
Edit/Delete/All for the cell  just below the so called last cell (say
C10) your solution will give 11 and not 10. What we are detecting is
the last row  that can be detected by the eyes.

I am just about to go out for an appointment, but how about this as an
outline.

I am assuming that the data is principally a single block of data, no
complete empty rows or complete empty columns.

Name the top left hand cell  something like "StartCell"

Record a macro  to give you a basic procedure which you can refeine later if
need be.


edit, Goto, select startcell

Hold Ctrl + * to select current Region

Edit, goto, special, blank  cells

That will select all the blank cells in the current region.

In the active cell type the = sign and select the cell immediate above

Press Ctrl + enter  to enter the relative formula into each of the blank
cells.

To polish off, Edit, Goto and select Startcell again.

The resulting macro should give you a good basis to develop further if you
need to.

Let me know how you get on.

I will check the list in the morning.

What you have suggested is a similar process of identifying the
Matrix area of the occupied spread sheet. Being a beginner of Ms
Excel and VBA I would like to thank you for showing me a way that I
have never used before. The process of using
Edit/Goto/Special/Blanks highlight all blank  cells and also show the
entire matrix of the occupied spread sheet. Your suggestion of
introducing cells with formula is not favorable because of the
possibility that cells might be linked to other sheets or workbook.

We are facing a problem that looks like an Excel design problem.
Allow me to explain.

(1)First let us create a new sheet with some data scattered within
the matrix A1:C10.
(2)If you do an Edit/Goto/Special/Blanks you will clearly see all
blank cells highlighted within the marked matrix A1:C10.
(3)Highlight A11:C13 and do an Edit/Clear/All.
(4)Repeat Edit/Goto/Special/Blanks you will see all blank cells
highlighted within the marked matrix A1:C13.
(5)Highlight rows 11 to 13 and do a delete
(6)The rows 11 to 13 are new inserted rows and should have no
information of "Clear All" for cells A11:C13 but if you repeat
Edit/Goto/Special/Blanks you will get the same result as in (4)
above.

Feel like telling Microsoft that they make us creating a
sledgehammer to crack a nut.

I am not sure I understand what you are getting at here.
Irrespective of whether you use Edit-Clear all or not on a selected range,
when you select the Edit-Goto-Special-Blanks, that will select all the blank
cells in the current selected range.

A neat manual trick here is to use standard entry procedures.

So a demo for you and anybody else interested here:

Select a new sheet

Type 123 into cells:

A1, A4, A7

B2, B5, B8

C3, C6, C9

Click into any cell  within the range say  B5

Press the Ctrl+* to select the current range

Select Edit-Goto-Special-Blanks

Note the active cell, should be B1

Type in 999

If

you press Enter, the next cell is selected in the selected range moving down
to the end of the current column  & then up to the top of the next column

Else

you press Tab, the next cell is selected in the selected range moving to the
right to the end of the current row  & then down to the start of the next row

NB: if you make a mistake, hold the Shift key and press either Tab or Enter
to move back. If you click into the area with the mouse or use arrows you
will loose your selection & have to start again, but omitting any cells that
you have successfully entered data into.

I have to say  sorry again. You are making the assumption that there
will be no blank  rows in between. Try delete cell  B5 and do the
Ctrl+* again. You will know what I mean.

I will be away on holiday for a week and will reply any message that
is meant for me after the holiday.