VBA code to detect the "longest" cell containg data and copying the whole range.

[monmon]

Hi all,

Can anyone with this? I am trying to do the following with VBA.

What i need is to find out the last cell with data and then selecting the whole range and copying it.

Please see the below.

As you can see, the column with the "longest" data range is B9,C9,D9,E9.

I need a VBA code to detect which Column has the longest Data and from there copy the entire range.

Hence, in this case, the range to be copied is From A2:J9.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column A
[/TD]
[TD]Column B
[/TD]
[TD]Column C
[/TD]
[TD]Column D
[/TD]
[TD]Column E
[/TD]
[TD]Column F
[/TD]
[TD]Column G
[/TD]
[TD]Column H
[/TD]
[TD]Column I
[/TD]
[TD]Column J
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]ttt
[/TD]
[TD]rrr
[/TD]
[TD]m
[/TD]
[TD]vvv
[/TD]
[TD]gg
[/TD]
[TD]ff
[/TD]
[TD]fff
[/TD]
[TD]fff
[/TD]
[TD]fff
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]yyy
[/TD]
[TD]hhh
[/TD]
[TD]ee
[/TD]
[TD]vdd
[/TD]
[TD][/TD]
[TD]ff
[/TD]
[TD]ff
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]ooo
[/TD]
[TD]eer
[/TD]
[TD]ehe
[/TD]
[TD]ss
[/TD]
[TD][/TD]
[TD]bb
[/TD]
[TD]fff
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]lll
[/TD]
[TD]ee
[/TD]
[TD]aa
[/TD]
[TD]gg
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]vvv
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]ppp
[/TD]
[TD]ee
[/TD]
[TD]dd
[/TD]
[TD]cc
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]jj
[/TD]
[TD]eee
[/TD]
[TD]xv
[/TD]
[TD]vv
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]ggg
[/TD]
[TD]yyy
[/TD]
[TD]nnn
[/TD]
[TD]bb
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]grr
[/TD]
[TD]mmm
[/TD]
[TD]bbb
[/TD]
[TD]ccb
[/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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Hence,in this case, the range to be copied is From A2 to J14.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column A
[/TD]
[TD]Column B
[/TD]
[TD]Column C
[/TD]
[TD]Column D
[/TD]
[TD]Column E
[/TD]
[TD]Column F
[/TD]
[TD]Column G
[/TD]
[TD]Column H
[/TD]
[TD]Column I
[/TD]
[TD]Column J
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]hjhjh
[/TD]
[TD]ghj
[/TD]
[TD]gh
[/TD]
[TD]ghj
[/TD]
[TD]ghj
[/TD]
[TD]ghj
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]fg
[/TD]
[TD]ghj
[/TD]
[TD]hj
[/TD]
[TD]ghj
[/TD]
[TD]ghj
[/TD]
[TD]ghjhj
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]fg
[/TD]
[TD]ghj
[/TD]
[TD]ghj
[/TD]
[TD]ghjghj
[/TD]
[TD]ghj
[/TD]
[TD]ghjghj
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]j
[/TD]
[TD]hj
[/TD]
[TD]hj
[/TD]
[TD]ghjghj
[/TD]
[TD]jhjg
[/TD]
[TD]ghj
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]ghj
[/TD]
[TD]ghj
[/TD]
[TD]hj
[/TD]
[TD]hj
[/TD]
[TD]ghj
[/TD]
[TD]ghj
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]gh
[/TD]
[TD]ghj
[/TD]
[TD]ghj
[/TD]
[TD]ghj
[/TD]
[TD]ghj
[/TD]
[TD]gh
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]ghj
[/TD]
[TD]ghj
[/TD]
[TD]ghj
[/TD]
[TD]gj
[/TD]
[TD]ghjgh
[/TD]
[TD]ghj
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]hjggh
[/TD]
[TD]ghj
[/TD]
[TD]jhj
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ghjh
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ghj
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]hj
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ghj
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]gh
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[VBA Geek]

Range("A2").CurrentRegion.Copy

 

[monmon]

Hi,

Thanks for your help!

However, the above raw data is pasted from another sheet. Hence, the CurrentRegion.Copy copies the entire range from A2 to J65000.

Anyone can help?
the code doesn't seem to ignore the blank cells.

 

[monmon]

Hi all,

Kindly help on this. the currentregion includes blank cells as due to the fact that the data was copied from another spreadsheet. Hence, it seems that currentregion doesn't seem to recognise the blank cells.

 

[monmon]

Anyone?

 

[monmon]

Anyone has a different method?