How to identify last Row which contains datas

[Nagoo]

Hi Friends / Rick,

My excel sheet has say 29 columns. I need to enter certain formula in the 30th column.

I have 'n' numbers of rows with contents. I need to find the last row which carries the contents and insert the formula in 'n16' cell to the upper cell.

But the problem is, the last row may not contain datas in all the cells (i,e 15 columns) only in one or two cells it may be available.

Please help me with a macro to find the last row which has the contents. Or it would be even better if i get the cell reference (i.e "n30") in which I need to insert the formula in last row.

Thanks for the help

 

[igold]

See if this does what you want...

Sub FindUsedRange()
  Dim FirstCell As Range, LastCell As Range
  Set LastCell = Cells(Cells.Find(What:="*", SearchOrder:=xlRows, _
      SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _
      Cells.Find(What:="*", SearchOrder:=xlByColumns, _
      SearchDirection:=xlPrevious, LookIn:=xlValues).Column)
  Set FirstCell = Cells(Cells.Find(What:="*", After:=LastCell, SearchOrder:=xlRows, _
      SearchDirection:=xlNext, LookIn:=xlValues).Row, _
      Cells.Find(What:="*", After:=LastCell, SearchOrder:=xlByColumns, _
      SearchDirection:=xlNext, LookIn:=xlValues).Column)
  MsgBox "The Address of the last cell is " & LastCell.Address
End Sub

HTH

igold

 

[JoeMo]

See if this does what you want...

Sub FindUsedRange()
  Dim FirstCell As Range, LastCell As Range
  Set LastCell = Cells(Cells.Find(What:="*", SearchOrder:=xlRows, _
      SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _
      Cells.Find(What:="*", SearchOrder:=xlByColumns, _
      SearchDirection:=xlPrevious, LookIn:=xlValues).Column)
  Set FirstCell = Cells(Cells.Find(What:="*", After:=LastCell, SearchOrder:=xlRows, _
      SearchDirection:=xlNext, LookIn:=xlValues).Row, _
      Cells.Find(What:="*", After:=LastCell, SearchOrder:=xlByColumns, _
      SearchDirection:=xlNext, LookIn:=xlValues).Column)
  MsgBox "The Address of the last cell is " & LastCell.Address
End Sub

HTH

igold

Your routine will fail to find a cell containing a formula that returns "" outside the "used range" that it currently finds. Better to change the xlValues to xlFormulas to catch this. If there are no such cells then making that change will still produce the result you get w/o the change.

 

[igold]

JoeMo,

Thanks for the tip. I appreciate the insight...

igold

 

[JoeMo]

JoeMo,

Thanks for the tip. I appreciate the insight...

igold

You are welcome - thanks for the reply.

 

[Nagoo]

HI Igold and Joe,

Thanks for the Macros and Tips. This works

Have a good day !

 

[Nagoo]

See if this does what you want...

Sub FindUsedRange()
  Dim FirstCell As Range, LastCell As Range
  Set LastCell = Cells(Cells.Find(What:="*", SearchOrder:=xlRows, _
      SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _
      Cells.Find(What:="*", SearchOrder:=xlByColumns, _
      SearchDirection:=xlPrevious, LookIn:=xlValues).Column)
  Set FirstCell = Cells(Cells.Find(What:="*", After:=LastCell, SearchOrder:=xlRows, _
      SearchDirection:=xlNext, LookIn:=xlValues).Row, _
      Cells.Find(What:="*", After:=LastCell, SearchOrder:=xlByColumns, _
      SearchDirection:=xlNext, LookIn:=xlValues).Column)
  MsgBox "The Address of the last cell is " & LastCell.Address
End Sub

HTH

igold

Hi Igold,

How do I select the specific cell which the Msgbox pops up ?? I mean, to select the cell which is available in Range 'LastCell.Address'

 

[igold]

Well other than just taking your cursor and putting it in the LastCell... you could add this line to the end of the code. Either before, after or instead of the last line that starts with "MsgBox"

LastCell.Select

igold

 

[Nagoo]

Thank you very much :D

 

[igold]

No problem, glad I could help. Thanks for the feedback!

igold