Solved

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

Posted on 2016-09-23
3
29 Views
Last Modified: 2016-09-23
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

0
Comment
Question by:kbay808
  • 2
3 Comments
 
LVL 19

Accepted Solution

by:
crystal (strive4peace) - Microsoft MVP, Access earned 500 total points
ID: 41813188
  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
 

Author Closing Comment

by:kbay808
ID: 41813223
It worked great...  Thank you
0
 
LVL 19
ID: 41813230
you're welcome ~ happy to help
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Being an active EE Expert means to get a lot of (E)EMail, as you certainly know. If you are using Outlook, I'll show you how to minimize your inbox contents without losing anything – even improve the experience by changing the Subject line to facili…
This is an Add-On procedure to be used in conjunction with the code provided in Reducing EE Email Clutter using Outlook (http://www.experts-exchange.com/Software/Office_Productivity/Groupware/Outlook/A_3146-Outlook-Processing-EE-emails-on-Receive.…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now