FINDING LAST ROW OF THE DATA
 

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

FINDING LAST ROW OF THE DATA
 

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

FINDING LAST ROW OF THE DATA
 

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

FINDING LAST ROW OF THE DATA
 

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

FINDING LAST ROW OF THE DATA
 

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

FINDING LAST ROW OF THE DATA
 

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


.

FINDING LAST ROW OF THE DATA
 

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


.

FINDING LAST ROW OF THE DATA
 

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/

.

FINDING LAST ROW OF THE DATA
 

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