finding the last filled cell

[Hursh]

Hi,

I m using vba/vbs to read an excel sheet

In the sheet there is column which I want to store in an array.
However how many rows wud be filled in this col is not fixed.

Is there any way to get to know how many cells in a col are filled or
to get the last filled cell in a col, wihtout actually parsing thru
each cell and testing for fill condition

tia
hursh

 

[Mark Rosenkrantz]

Hursh;

Try : Selection.End(xlDown).Select

Mark.

 

[JE McGimpsey]

One way:

Dim numCellsFilled As Long
Dim lastCell As Range

numCellsFilled = Application.CountA(Columns(1))
Set lastCell = Cells(Rows.Count, 1).End(xlUp)

 

[Bob Phillips]

Hursh,

On the second item, I always use the format

Set oLast = Cells(Rows.Count,"A").End(xlUp)

as it is more easily readable with the column letter.

This esentially starts at the foot of the column and looks up to the first
cell it finds with data, thereby taking care of any embedded empty cells in
the column.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Hursh]

thank you all for ur suggestions

I also found this way to get to know total number of cols as well as
rows, though this one gives the max no of rows in any col and max
number of cols for any row.

dim usedRange
set usedRange=xlobj.Worksheets("Sheet1").UsedRange

totalRows=usedRange.Rows.Count
totalCols=usedRange.Columns.Count

regards
Hursh