Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

small code to find last column with data

This is a discussion on small code to find last column with data within the Excel Questions forums, part of the Question Forums category; The EntireColumn property returns range, say, X1:X1048576. You need only 42 rows (rows 9 through 50), so EntireColumn.Resize(50 - 9 ...

  1. #11
    Board Regular Tetra201's Avatar
    Join Date
    Oct 2016
    Posts
    2,448

    Default Re: small code to find last column with data

    The EntireColumn property returns range, say, X1:X1048576.
    You need only 42 rows (rows 9 through 50), so EntireColumn.Resize(50 - 9 + 1) returns range X1:X42.
    You need rows 9 through 50, so EntireColumn.Resize(50 - 9 + 1).Offset(9 - 1) returns range X9:X50.

  2. #12
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    28,129

    Default Re: small code to find last column with data

    Quote Originally Posted by Tetra201 View Post
    Or this:
    Code:
        Sheets("LE").Cells.Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).EntireColumn.Resize(50 - 9 + 1).Offset(9 - 1).Copy
        Sheets("CD").Cells(4, 5).PasteSpecial xlPasteValues
        Application.CutCopyMode = False
    Or even this one-liner...
    Code:
    Intersect(Sheets("LE").Cells.Find("*", , , , xlByColumns, xlPrevious).EntireColumn, Sheets("LE").Rows("9:50")).Copy Sheets("CD").Range("E4")
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See here.

  3. #13
    Board Regular
    Join Date
    Oct 2008
    Posts
    788

    Default Re: small code to find last column with data

    @ Rick, thanks , I'll try that too. Something more to learn from you again.
    @tetra, thanks for explanation. Unfortunately I could not understand it . What is 50-9+1 ? How does it work? Is it because 50 minus 9 =41 then + 1= 42? It's kind of difficult to guess how it actually works
    Last edited by snjpverma; Apr 21st, 2017 at 02:13 PM.

  4. #14
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    28,129

    Default Re: small code to find last column with data

    Quote Originally Posted by snjpverma View Post
    @tetra, thanks for explanation. Unfortunately I could not understand it . What is 50-9+1 ? How does it work? Is it because 50 minus 9 =41 then + 1= 42? It's kind of difficult to guess how it actually works
    You wanted to copy rows 9 through 50 which is a total of 42 cells. Personally, I would have just used 42 as the argument for the Resize property because we know that is a fixed amount. What tetra did is show you how to calculate the 42... row 50 minus row 9 gets you the difference and plus 1 more gets you the count. My code line removes the need to calculate the number of cells (or the offset) as it simply creates the range by intersecting the entire last column with rows 9 through 50 which it then copies to the appropriate starting output cell.
    Last edited by Rick Rothstein; Apr 21st, 2017 at 02:24 PM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See here.

  5. #15
    Board Regular
    Join Date
    Oct 2008
    Posts
    788

    Default Re: small code to find last column with data

    Thanks for the explanation Rick. Highly appreciate it

Page 2 of 2 FirstFirst 12

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com