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 ...
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.
Rick's "mini" blog... http://www.excelfox.com/forum/f22/
.
Want to post a small screen shot? See here.
@ 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.
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.
Thanks for the explanation Rick. Highly appreciate it
Like this thread? Share it with others