Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Macro: Expand Range Rows & Columns

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Macro: Expand Range Rows & Columns

    Hi All,

    I am attempting to take a data matrix, and convert it into a standardised version to feed into a model. The matrix contains Population Predictions, and therefore has Ages as row Headings, and Years as column Headings. The awkward part is that the row and column headings are not explicitly adjacent to the data matrix.

    To start with, I have located the row and column headings, and stored them as ranges, rAge and rYear respectivley. I now want to define the range rData to contain the matrix data so that I can copy the matrix and headings into my standardised form.

    I can find the first cell of the range easily by using the code:
    Code:
    sub TopLeft()
    Cells(rAge.Row,rYear.Column).Select
    end sub
    but the other cell is not as simple. Ideally I would like a property similar to .Row which gives the last row of the current range.

    Currently the only way I can think of doing this is to use string operations on the range's address to find the last row/column but I hope there is a much more elegant way.

    Thanks for your time.

    Alan

  • #2
    Re: Finding The Last Column/row Of A Specific Range

    Hi

    Sub TopLeft() 
    Dim rngData as Range
    Set rngData = Cells(rAge.Row,rYear.Column).Resize(rAge.Count,rYear.Count)
    End Sub


    Wigi
    Regards,

    Wigi

    Excel MVP 2011-2014

    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

    Comment


    • #3
      Re: Finding The Last Column/row Of A Specific Range

      Cheers Wigi! That did it spot on.

      Alan

      Comment

      Trending

      Collapse

      There are no results that meet this criteria.

      Working...
      X