Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 3 of 3

Thread: Macro: Expand Range Rows & Columns

  1. #1
    Join Date
    15th July 2006
    Location
    UK
    Posts
    4

    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:
    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,042

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

    Hi

    VB:
    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, 2012, 2013



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

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

  3. #3
    Join Date
    15th July 2006
    Location
    UK
    Posts
    4

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

    Cheers Wigi! That did it spot on.

    Alan

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Expand Range In Macro
    By Six Strings in forum EXCEL HELP
    Replies: 5
    Last Post: October 16th, 2007, 13:53
  2. Numbers Of Rows/Columns in Range
    By cpanon in forum EXCEL HELP
    Replies: 7
    Last Post: July 8th, 2007, 03:54
  3. Expand complex graph range via macro
    By sqelady in forum EXCEL HELP
    Replies: 3
    Last Post: February 13th, 2006, 06:45
  4. Rows expand/contract...Formula & Formats
    By Dave196 in forum EXCEL HELP
    Replies: 5
    Last Post: January 22nd, 2006, 06:47
  5. Expand range for Excel Macro using VBA
    By ROVER1 in forum EXCEL HELP
    Replies: 14
    Last Post: April 16th, 2003, 00:33

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
  •  
porno