Results 1 to 3 of 3

Thread: Copy A Dynamically Range Depending On Last Filled Non-Empty Cell In That Column

  1. #1
    Junior Member
    Join Date
    Jun 2013
    Posts
    5
    Rep Power
    0

    Copy A Dynamically Range Depending On Last Filled Non-Empty Cell In That Column

    I am trying to use a variable in a range of cells. IE Range(a2:z100).select... I would like to set something like MyRange=1000 and set use Range(a2:Myrange).select. that way when MyRange changes i can make 1 change in the Macro and still have it run like i want.

  2. #2
    Moderator
    Join Date
    Jul 2012
    Posts
    156
    Rep Power
    13
    What is it you're trying to do because in 99% of the cases it isn't necessary to SELECT a range when writing code.
    It's obselete and slows down the execution.
    You don't write
    Code:
    Range("A2:Z1000").Select
    Selection.Copy
    Sheets("DestinationSheet").Select
    Range("A1").Select
    Selection.Paste
    when copying a range but
    Code:
    Range("A2:Z1000").Copy Sheets("DestinationSheet").Range("A1")
    So when determining the address of an entire Range you write something like
    Code:
    With Sheets("Sheet1")
        .Range("A2:Z" & .Cells(.Rows.Count, 1).End(xlUp).Row).Copy Sheets("DestinationSheet").Range("A1")
    End With
    where 1 stands for the columnnumber in which in every row always a value is entered.
    Last edited by Excel Fox; 06-22-2013 at 10:55 AM. Reason: Removed (1) From End(xlUp)(1) as it's the same

  3. #3
    Junior Member
    Join Date
    Jun 2013
    Posts
    5
    Rep Power
    0
    Thanks for the quick response
    i will play with that. :-)

Similar Threads

  1. Replies: 10
    Last Post: 06-20-2013, 12:21 AM
  2. Replies: 7
    Last Post: 04-21-2013, 07:50 PM
  3. Lookup From Cell Range By Matching Row and Column
    By paul_pearson in forum Excel Help
    Replies: 2
    Last Post: 03-07-2013, 02:02 PM
  4. Get last Filled Cell address in a Range.
    By Rajan_Verma in forum Rajan Verma's Corner
    Replies: 3
    Last Post: 03-24-2012, 01:08 AM
  5. Last Filled Cell Having Text
    By Excel Fox in forum Excel and VBA Tips and Tricks
    Replies: 5
    Last Post: 07-18-2011, 02:33 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •