How to modify my VBA code to select a range that will stop on the first row that has no data?

The below code works perfect, but the range is fixed.  Instead of range "L2:L2000" I want to make the range "l2: the last row with data".

Range(Sheet3.Range("L2:L2000").Find("No"), Sheet3.Range("L" & Rows.Count).End(xlUp)).EntireRow.Resize(, 10).Copy
Sheet2.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues

Open in new window

kbay808Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
crystal (strive4peace) - Microsoft MVP, AccessConnect With a Mentor Remote Training and ProgrammingCommented:
  dim nRow2 As Long 
   With Sheet3 ' or sheet reference
      nLastRow  = .Cells(.Rows.Count, 12).End(xlUp).Row 'L is column 12
   End With

Open in new window

then use nLastRow instead of 2000 -->
"L2:L" & nLastRow

Open in new window

0
 
kbay808Author Commented:
It worked great...  Thank you
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome ~ happy to help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.