none
Table header - Currrent Region RRS feed

  • Question

  • Hi

    I have a table with 5 columns and 40 rows (this may vary). The first row is a header row.

    I have the following code which loads the table into a Sector array and because of the offset statement ignores the heading.

    I was hoping to have 39 rows in my array - however I have 40 (because of the offset) - wit the last row being empty.

    Dim Sector As Variant
    Sector = Sheet1.Range("A1").CurrentRegion.Offset(1, 0)

    Is there a way that I can create my array without the header but with only the 39 rows?

    Any help would be welcome.

    Many thanks.

    Peter

    Tuesday, September 20, 2016 3:05 PM

Answers

  • Like this:

        With Sheet1.Range("A1").CurrentRegion
            Sector = .Offset(1).Resize(.Rows.Count - 1)
        End With


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by Chenchen LiModerator Wednesday, September 21, 2016 2:19 AM
    • Marked as answer by py1 Wednesday, September 21, 2016 8:53 AM
    Tuesday, September 20, 2016 3:54 PM
  • Hi,

    You could also visit Range.CurrentRegion Property (Excel), you would find:

    This example assumes that you have a table on Sheet1 that has a header row. The example selects the table, without selecting the header row. The active cell must be somewhere in the table before you run the example.

     

    Set tbl
    = ActiveCell.CurrentRegion 
    
    tbl.Offset(1,
    0).Resize(tbl.Rows.Count - 1, _ 
    
     tbl.Columns.Count).Select

    • Marked as answer by py1 Wednesday, September 21, 2016 8:54 AM
    Wednesday, September 21, 2016 2:21 AM
    Moderator
  • You could do it like this:

        With Sheet1.Range("A1").CurrentRegion
            .Sort Key1:=Sheet1.Range("A1"), Header:=xlYes
            Sector = .Offset(1).Resize(.Rows.Count - 1)
        End With


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by py1 Friday, October 7, 2016 1:00 PM
    Friday, October 7, 2016 12:40 PM

All replies

  • Like this:

        With Sheet1.Range("A1").CurrentRegion
            Sector = .Offset(1).Resize(.Rows.Count - 1)
        End With


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by Chenchen LiModerator Wednesday, September 21, 2016 2:19 AM
    • Marked as answer by py1 Wednesday, September 21, 2016 8:53 AM
    Tuesday, September 20, 2016 3:54 PM
  • Hi,

    You could also visit Range.CurrentRegion Property (Excel), you would find:

    This example assumes that you have a table on Sheet1 that has a header row. The example selects the table, without selecting the header row. The active cell must be somewhere in the table before you run the example.

     

    Set tbl
    = ActiveCell.CurrentRegion 
    
    tbl.Offset(1,
    0).Resize(tbl.Rows.Count - 1, _ 
    
     tbl.Columns.Count).Select

    • Marked as answer by py1 Wednesday, September 21, 2016 8:54 AM
    Wednesday, September 21, 2016 2:21 AM
    Moderator
  • thanks Hans - very elegant.
    Wednesday, September 21, 2016 8:53 AM
  • Hi Hans,

    You kindly provide this neat solution to a problem I had about 3 weeks ago.

    With Sheet1.Range("A1").CurrentRegion
            Sector = .Offset(1).Resize(.Rows.Count - 1)
     End With

    I would like to add a sort to this so that I could sort Sheet1 based on the values in column A and then apply this fix.

    Should I sort the sheet before running this code or is this a better way to do this?

    many thanks

    Peter

    Friday, October 7, 2016 11:48 AM
  • You could do it like this:

        With Sheet1.Range("A1").CurrentRegion
            .Sort Key1:=Sheet1.Range("A1"), Header:=xlYes
            Sector = .Offset(1).Resize(.Rows.Count - 1)
        End With


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by py1 Friday, October 7, 2016 1:00 PM
    Friday, October 7, 2016 12:40 PM
  • many thanks Hans - works perfectly!

    kind regards,

    Peter

    Friday, October 7, 2016 1:00 PM