Hi

I have a worksheet with data, the data in each column is
not continous.

I want to find the last row of the data (the data is in
Column A to Column R) the data is not continus in any of
the columns.

Can anyone help in writing a VBA code in finding the last
row of data

thanks in advance

If you want the last used row or column anywhere in the sheet, then try the
following:-

lcol = ActiveSheet.UsedRange.Column - 1 + _
ActiveSheet.UsedRange.Columns.Count

lrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count


If you want the last cell in a particular Column, then try:-

lrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

If you want the last cell in a particular Row, then try:-

lcol = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"SUDHENDRA" wrote in message
...
Hi

I have a worksheet with data, the data in each column is
not continous.

I want to find the last row of the data (the data is in
Column A to Column R) the data is not continus in any of
the columns.

Can anyone help in writing a VBA code in finding the last
row of data

thanks in advance


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.558 / Virus Database: 350 - Release Date: 02/01/2004

I asked this question not long ago and my personal favorite answer is:

colCount = ActiveSheet.UsedRange.Columns.Count

It also works with Rows. - Piku

--
Message posted from http://www.ExcelForum.com

I don't like Usedrange because it will not always give the last row with data

You can use this funtion also

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

You can use this in your code then for example
Lr = LastRow(Sheets("Sheet2"))





--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"SUDHENDRA" wrote in message ...
Hi

I have a worksheet with data, the data in each column is
not continous.

I want to find the last row of the data (the data is in
Column A to Column R) the data is not continus in any of
the columns.

Can anyone help in writing a VBA code in finding the last
row of data

thanks in advance

Won't always work though.

Open up a new sheet, populate cells D10:F15 and then try that code. It will
give you 3 for the last column (Should be 6) and 6 for the last row (Should be
15). It counts the rows/columns *within* the UsedRange and does not necessarily
give you the last of either.

lcol = ActiveSheet.UsedRange.Column - 1 + _
ActiveSheet.UsedRange.Columns.Count

lrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count

Take lcol with example ranges I gave you above:-

ActiveSheet.UsedRange.Column will give you the first column in the range, ie 4
ActiveSheet.UsedRange.Columns.Count will give you the number of columns in that
range ie 3
Add the first column to the number of columns and you will always have 1 more
than the last column, hence the -1 in there. Same principle for rows.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"pikus " wrote in message
...
I asked this question not long ago and my personal favorite answer is:

colCount = ActiveSheet.UsedRange.Columns.Count

It also works with Rows. - Pikus


---
Message posted from http://www.ExcelForum.com/



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.558 / Virus Database: 350 - Release Date: 02/01/2004

Thanks
-----Original Message-----
I don't like Usedrange because it will not always give
the last row with data

You can use this funtion also

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious,
_
MatchCase:=False).Row
On Error GoTo 0
End Function

You can use this in your code then for example
Lr = LastRow(Sheets("Sheet2"))





--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"SUDHENDRA" wrote
in message ...
Hi

I have a worksheet with data, the data in each column
is
not continous.

I want to find the last row of the data (the data is in
Column A to Column R) the data is not continus in any
of
the columns.

Can anyone help in writing a VBA code in finding the
last
row of data

thanks in advance


.

THANKS
-----Original Message-----
Won't always work though.

Open up a new sheet, populate cells D10:F15 and then try
that code. It will
give you 3 for the last column (Should be 6) and 6 for
the last row (Should be
15). It counts the rows/columns *within* the UsedRange
and does not necessarily
give you the last of either.

lcol = ActiveSheet.UsedRange.Column - 1 + _
ActiveSheet.UsedRange.Columns.Count

lrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count

Take lcol with example ranges I gave you above:-

ActiveSheet.UsedRange.Column will give you the first
column in the range, ie 4
ActiveSheet.UsedRange.Columns.Count will give you the
number of columns in that
range ie 3
Add the first column to the number of columns and you
will always have 1 more
than the last column, hence the -1 in there. Same
principle for rows.

--
Regards
Ken....................... Microsoft MVP -
Excel
Sys Spec - Win XP Pro / XL 00/02/03

---------------------------------------------------------
-------------------
It's easier to beg forgiveness than ask permission :-)
---------------------------------------------------------
-------------------



"pikus " wrote in
message
...
I asked this question not long ago and my personal
favorite answer is:

colCount = ActiveSheet.UsedRange.Columns.Count

It also works with Rows. - Pikus


---
Message posted from http://www.ExcelForum.com/



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system
(http://www.grisoft.com).
Version: 6.0.558 / Virus Database: 350 - Release Date:
02/01/2004


.

THANKS
-----Original Message-----
I asked this question not long ago and my personal
favorite answer is:

colCount = ActiveSheet.UsedRange.Columns.Count

It also works with Rows. - Pikus


---
Message posted from http://www.ExcelForum.com/

.

You're welcome :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"SUDHENDRA" wrote in message
...
THANKS
-----Original Message-----
Won't always work though.

Open up a new sheet, populate cells D10:F15 and then try
that code. It will
give you 3 for the last column (Should be 6) and 6 for
the last row (Should be
15). It counts the rows/columns *within* the UsedRange
and does not necessarily
give you the last of either.

lcol = ActiveSheet.UsedRange.Column - 1 + _
ActiveSheet.UsedRange.Columns.Count

lrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count

Take lcol with example ranges I gave you above:-

ActiveSheet.UsedRange.Column will give you the first
column in the range, ie 4
ActiveSheet.UsedRange.Columns.Count will give you the
number of columns in that
range ie 3
Add the first column to the number of columns and you
will always have 1 more
than the last column, hence the -1 in there. Same
principle for rows.

--
Regards
Ken....................... Microsoft MVP -
Excel
Sys Spec - Win XP Pro / XL 00/02/03

---------------------------------------------------------
-------------------
It's easier to beg forgiveness than ask permission :-)
---------------------------------------------------------
-------------------



"pikus " wrote in
message
...
I asked this question not long ago and my personal
favorite answer is:

colCount = ActiveSheet.UsedRange.Columns.Count

It also works with Rows. - Pikus


---
Message posted from http://www.ExcelForum.com/



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system
(http://www.grisoft.com).
Version: 6.0.558 / Virus Database: 350 - Release Date:
02/01/2004


.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004