small code to find last column with data

[snjpverma]

The below macro always returns zero instead of the last column number with any data in it.

Sub Findcolumn()
Dim Lastcolumn As Range, ws As Worksheet
With ThisWorkbook.Worksheets("AB")

Set Lastcolumn = .Cells.Find(What:="*", After:=.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False)

End With
End Sub

Edit : Basically I want to Copy last used column of sheet(XYZ) to sheet (ABC)

 

[Tetra201]

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 Rothstein]

Or this:

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

[table="width: 500"]
[tr]
	[td]Intersect(Sheets("LE").Cells.Find("*", , , , xlByColumns, xlPrevious).EntireColumn, Sheets("LE").Rows("9:50")).Copy Sheets("CD").Range("E4")[/td]
[/tr]
[/table]

 

[snjpverma]

@ 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

 

[Rick Rothstein]

@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.

 

[snjpverma]

Thanks for the explanation Rick. Highly appreciate it