Using merged cell data in vba

keiranwyllie

New Member
Joined
May 12, 2017
Messages
32
Hi all,

Is there a catch to using Target.Cells.Count >1?

I understand there's some issues around vba and merged cells and this piece of code can be used to ignore merged cells.

Is there a way to use this such that
VBA Code:
If Target.Cells.Count > 1 Then
    'set the Target value to the actual value of the merged cell
    Target = Target.Address.Value     'I know this doesn't work
End if

I'm aiming to use it in some logging code but the worksheet does make use of merged cells for data entry.

Cheers.
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
I would advise against using
VBA Code:
If Target.Cells.Count > 1 Then
for the simple reason that it will crash if there are too many cells, instead use
VBA Code:
If Target.CountLarge > 1 Then
But as you have realised that will not work if you have merged cells. Just another reason why merged cells should be avoided like the plague.
 

keiranwyllie

New Member
Joined
May 12, 2017
Messages
32
I was able to implement it in a different manner by using .Value = Target.Address to pull the cell reference of the merged cells.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,131
Messages
5,628,885
Members
416,349
Latest member
salmanb74

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top