small code to find last column with data - Page 2

Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

Thread: small code to find last column with data

  1. #11
    MrExcel MVP Tetra201's Avatar
    Join Date
    Oct 2016
    Posts
    2,851
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    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
    30,489
    Post Thanks / Like
    Mentioned
    34 Post(s)
    Tagged
    5 Thread(s)

    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 Part B here.

  3. #13
    Board Regular
    Join Date
    Oct 2008
    Posts
    824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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 03:13 PM.

  4. #14
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    30,489
    Post Thanks / Like
    Mentioned
    34 Post(s)
    Tagged
    5 Thread(s)

    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 03:24 PM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  5. #15
    Board Regular
    Join Date
    Oct 2008
    Posts
    824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: small code to find last column with data

      
    Thanks for the explanation Rick. Highly appreciate it

User Tag List

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