Left function for entire column

[GirishDhruva]

Hi Everyone ,
Here i am trying to find the left-function of a particular column i,e 'A', and i did it for destination column, but i need that at last column were i would not be knowing the last column.

Sub Leftfunc()


Dim Sourcecolumn As Range
Dim Destinationcolumn As Range
Dim i As Integer


Set Sourcecolumn = Sheet1.Range("A4:A100")' i would not be knowing the range also, just i have mentioned 100 from A4
Set Destinationcolumn = Sheet1.Range("H4:H100") ' if i need at last column means how can i do that
For i = 1 To Sourcecolumn.Count
Destinationcolumn(i, 1).Value = Left(Sourcecolumn(i, 1).Value, 2)
Next i


End Sub

Thanks in prior

 

[Michael M]

So you want the destination column ( last column) to have the first 2 charatcers of Col "A"

Sub Leftfunc()
Dim lr As Long, lc As Integer, r As Long
lc = Cells(1, Columns.Count).End(xlToLeft).Column
lr = Cells(Rows.Count, lc).End(xlUp).Row
    For r = 1 To lr
        Cells(r, lc).Value = Left(Cells(r, 1).Value, 2)
    Next r
End Sub

 

[Rick Rothstein]

So you want the destination column ( last column) to have the first 2 charatcers of Col "A"

Sub Leftfunc()
Dim lr As Long, lc As Integer, r As Long
lc = Cells(1, Columns.Count).End(xlToLeft).Column
lr = Cells(Rows.Count, lc).End(xlUp).Row
    For r = 1 To lr
        Cells(r, lc).Value = Left(Cells(r, 1).Value, 2)
    Next r
End Sub

Here is another way to write your function assuming the OP really meant he wanted to overwrite the existing values in the last column with the first two characters from Column A...

[table="width: 500"]
[tr]
	[td]Sub Leftfunc()
  Dim LastRow As Long, LastCol As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  LastCol = Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column
  Range("A4").Resize(LastRow - 3).Offset(, LastCol [B][COLOR="#FF0000"]- 1[/COLOR][/B]) = Evaluate("IF({1},LEFT(A4:A" & LastRow & ",2))")
End Sub[/td]
[/tr]
[/table]

However, my gut tells me the OP probably wanted the output to go to the empty column immediately after the last column of data. If so, then he would need to remove the -1 (highlighted in red) from the code above.

 

[GirishDhruva]

So you want the destination column ( last column) to have the first 2 charatcers of Col "A"

Sub Leftfunc()
Dim lr As Long, lc As Integer, r As Long
lc = Cells(1, Columns.Count).End(xlToLeft).Column
lr = Cells(Rows.Count, lc).End(xlUp).Row
    For r = 1 To lr
        Cells(r, lc).Value = Left(Cells(r, 1).Value, 2)
    Next r
End Sub

This is overlapping the last column, but i need the values in last empty column and can u please explain the code, so that i can learn

Thanks in prior

 

[GirishDhruva]

Here is another way to write your function assuming the OP really meant he wanted to overwrite the existing values in the last column with the first two characters from Column A...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub Leftfunc()
  Dim LastRow As Long, LastCol As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  LastCol = Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column
  Range("A4").Resize(LastRow - 3).Offset(, LastCol [B][COLOR=#FF0000]- 1[/COLOR][/B]) = Evaluate("IF({1},LEFT(A4:A" & LastRow & ",2))")
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

However, my gut tells me the OP probably wanted the output to go to the empty column immediately after the last column of data. If so, then he would need to remove the -1 (highlighted in red) from the code above.

Thank you sir , it worked exactly how i needed

 

[Michael M]

Rick
I agree !!

However, my gut tells me the OP probably wanted the output to go to the empty column immediately after the last column of data