• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 43
  • Last Modified:

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

0
kbay808
Asked:
kbay808
  • 2
1 Solution
 
crystal (strive4peace) - Microsoft MVP, AccessRemote 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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now