• 0

Exce VBA


Question

2 answers to this question

Recommended Posts

  • 0

the issue is here is that merged cells are not actually a single cell, they are a cell range...

this question has come up before, and i don't think there was an answer then, personally i would try to avoid using merged cells if possible

Link to comment
Share on other sites

  • 0

having said that, i persevered, and this works given the limited testing i have done :)

Public Function CountIncMerge(rCol As Range, rRange As Range) As Integer

	Dim d As Double

	For Each c In rRange
		' check if colours match
		If c.Interior.ColorIndex = rCol.Interior.ColorIndex Then
			'if the cells are merged and it isn't the only cell selected
			If c.MergeCells And rRange.Count > 1 Then
				'add 1 to the total count for the whole merge area
				d = d + (1 / c.MergeArea.Count)
			Else
				'else add 1
				d = d + 1
			End If
		End If
	Next c

	'return the result
	CountIncMerge = d
End Function

ok, so it's not the prettiest of code, more of a hack really, but it works... ;)

it takes in two params, 1st a colour to check for, and 2nd a range to check in

post-24841-1254182582.png

merge_cell_count.zip

Edited by BGM
Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.