+ Reply to Thread
Results 1 to 5 of 5

How do you make an open-ended/dynamic range?

  1. #1
    Forum Contributor
    Join Date
    02-15-2012
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    141

    How do you make an open-ended/dynamic range?

    I'm slowly learning how to use VBA and could use a bit of help understanding this: How do I create a dynamic range that depends on the number of columns in the sheet? For instance, I wanted to add an Auto Filter to my report that has 5 columns, so I wrote

    Range ("A1:E1").AutoFilter

    Since this particular report might have 6, 7, 8, 9, 10+ columns, I need to write the code so that it can count the columns and automatically create the correct range.

    If you have the time, could you walk me through the steps for doing this?

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: How do you make an open-ended/dynamic range?

    May be

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    02-15-2012
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    141

    Re: How do you make an open-ended/dynamic range?

    Thanks.

    Can you explain what's going on in this part "& Cells(1, Columns.Count)"

    What happens when you add the "&" symbol to Range("A1"), and what specifically is Excel doing when it sees (1, Columns.count)?

  4. #4
    Forum Contributor
    Join Date
    02-15-2012
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    141

    Re: How do you make an open-ended/dynamic range?

    Also, why are you using .End(xlToLeft)? Wouldn't you want to go to the right?

  5. #5
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: How do you make an open-ended/dynamic range?

    If your data has a single header row and is surrounded by empty rows and columns (as it should be), you can use

    Please Login or Register  to view this content.
    Entia non sunt multiplicanda sine necessitate

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1