Extend Range To Longest Column

  • I had asked about automatically naming regions and this is an extension of that post because it's closed. The code we ended up with to name the region is:

    My problem now is that I need to have the regions be the length of the longest column. I've tried using a few different ways using the worksheet range but I can't seem to get it to work.
    Thanks in advance for the help!

  • Re: Changing Region Size In Vba


    This might do it:

    Code
    sRT = "=offset(" _
    & Target.Address & _
    ",1,0,counta(" _
    & Cells(2, Target.Column).Resize(Target.CurrentRegion.Rows.Count - Target.Row).Address & _
    ") )"


    p45cal

  • Re: Changing Region Size In Vba


    If you mean


    http://www.ozgrid.com/forum/showthread.php?t=71733


    It isn't closed. Stick to one thread until the question is solved!

  • Re: Changing Region Size In Vba


    p45cal: I tried that code buy just substituting it in for the line that started the same way. I ran into an error, am I using your code wrong?
    royUK: "Automatically Naming Regions" would not let me post when I tried. It said it had expired (I think that's the term it used).
    ByTheCringe2: You're right that it is a slightly different question, thanks for sticking up for me. ;)

  • Re: Changing Region Size In Vba


    Use this perhaps;

  • Re: Extend Range To Longest Column


    For a quick hack, SN, see if this change works:


    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

  • Re: Extend Range To Longest Column


    re: "p45cal: I tried that code buy just substituting it in for the line that started the same way. I ran into an error, am I using your code wrong?"


    What error? What line?
    Make sure nothing's been mangled in you copy/pasting. I always test code before posting, and if I haven't, I'm explicit about it.
    If it still fails, post that code as you copied it and a few lines above and below (or even the whole macro if it's not too long) saying what the error says and what line it stops at.


    p45cal

  • Re: Extend Range To Longest Column


    I'm getting an error on the line beginning with sRT it is an "Object Required" error. Here is the entire macro:

    Could it be a problem because it's being run from a module?

  • Re: Extend Range To Longest Column


    Quote from StillNew

    Could it be a problem because it's being run from a module?


    Yes, there's no longer a Target object. Goal posts moved, so try this, it might work (depending on Currentregion giving the full region, which in turn depends on no complete blank rows and columns:


    p45cal

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!