FINDING LAST ROW OF THE DATA

  • Thread starter Thread starter SUDHENDRA
  • Start date Start date
S

SUDHENDRA

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
 
I asked this question not long ago and my personal favorite answer is:

colCount = ActiveSheet.UsedRange.Columns.Count

It also works with Rows. - Piku
 
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"))
 
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.
 
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)




"SUDHENDRA" <[email protected]> wrote
in message news:[email protected]...
 
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 :-)
--------------------------------------------------------- -------------------






---
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


.
 
Back
Top