Excel can't find last row of data

**awbf** · 03-05-2009, 06:01 PM

Good afternoon, I am having an issue where excel is not going to the last row of data. The code line I am using is the following.

ActiveCell.SpecialCells(xlLastCell).Select
           lrow = ActiveCell.Row
           Lcol = ActiveCell.Column

Range("AJ2:BS2").Select
    Selection.Copy
    Range("AJ3").Select
    Range("AJ3:AJ" & lrow).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

What happens is when it looks for the last row of data it goes 3 or 4 rows past the last row of actual data. Any thoughts on how to get past the "dirty" cells? Thanks in advance.

AWBF

**VBA Noob** · 03-05-2009, 06:11 PM

Maybe reset the used range

http://www.contextures.com/xlfaqApp.html#Unused

VBA Noob

**awbf** · 03-05-2009, 06:40 PM

Thanks VBA Noob
I added the module to my macro but it still does not clear the problem. I still get 3 lines below the data. Any other thoughts??

**VBA Noob** · 03-05-2009, 06:45 PM

Not without taking a look at the workbook. Post the sheet removing any confidential info

VBA Noob

**VBA Noob** · 03-05-2009, 06:53 PM

Or see if this helps to determine the last cell instead of LastCell

Dim LastCell As Range
Set LastCell = Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious)
    MsgBox LastCell.Row
    MsgBox LastCell.Column

VBA Noob

**awbf** · 03-05-2009, 10:00 PM

Thanks VBA Noob
I tried the code and it finds the rows perfect. Now when I apply it to my formula I get an error. Error is:
LastCell.Row=<Object Required>

Dim LastCell As Range
    Set LastCell = Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious)

Range("AJ2:BS2").Select
    Selection.Copy
    Range("AJ3").Select
    Range("AJ3:AJ" & LastCell.Row).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

This is where I am a noob, not sure what I should be adding to the code???