Thanks:  0
Likes:  0

# Thread: small code to find last column with data

1. ## 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. ## Re: small code to find last column with data

Originally Posted by Tetra201
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")

```

3. ## 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

4. ## Re: small code to find last column with data

Originally Posted by snjpverma
@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.

5. ## Re: small code to find last column with data

Thanks for the explanation Rick. Highly appreciate it

## User Tag List

#### Posting Permissions

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