VBA macro to paste in last row of A,B etc column by finding used row in multiple columns

[Sathish G]

Hi,

I need a macro code to paste the values as below of last used rows by finding in given 6 columns.
For example:
Here banana columns used more rows so based on more used i need your support to paste apple columns values on below.

Please help me to get VBA code for below scenario.



[TABLE="width: 500"]
<tbody>[TR]
[TD]apple[/TD]
[TD]16[/TD]
[TD]orange[/TD]
[TD]20[/TD]
[TD]banana[/TD]
[TD]28[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]16[/TD]
[TD][/TD]
[TD]25[/TD]
[TD][/TD]
[TD]67[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]17[/TD]
[TD][/TD]
[TD]23[/TD]
[TD][/TD]
[TD]78[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]56[/TD]
[TD][/TD]
[TD]98[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]87[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]81[/TD]
[TD]orange[/TD]
[TD]87[/TD]
[TD]banana[/TD]
[TD]67[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD]apple[/TD]
[TD]16[/TD]
[TD]orange[/TD]
[TD]20[/TD]
[TD]banana[/TD]
[TD]28[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]16[/TD]
[TD][/TD]
[TD]25[/TD]
[TD][/TD]
[TD]67[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]17[/TD]
[TD][/TD]
[TD]23[/TD]
[TD][/TD]
[TD]78[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]56[/TD]
[TD][/TD]
[TD]98[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]87[/TD]
[/TR]
</tbody>[/TABLE]

 

[XLfriend777]

Hi Sathish G - You might try determining the last row using code like the following:

Sub Range_Find_Method()
'Finds the last non-blank cell on a sheet/range.

Dim lRow As Long
Dim lCol As Long
    
    lRow = Cells.Find(What:="*", _
                    After:=Range("A1"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row
    
    MsgBox "Last Row: " & lRow

End Sub