Detecting merged cells

M

microsoft

I have written a macro that searches a worksheet for lines of data that are
missing data from one column. It works except for when it reaches a header
half way down the sheet that is a merged cell 5 cells wide. It treats this
as 1 cell and so my conting system goes ary. How do you either deal with
this or detect the fact that the activecell is part of a merged group of
cells?

I am new to this so don't worry about explaining in simple terms.

Thanks in advance
 
C

Chip Pearson

Use the MergeArea property to determine whether a cell is merged.

If ActiveCell.MergeArea.Address <> ActiveCell.Address Then
Debug.Print "Cell merged"
Else
Debug.Print "Cell not merged"
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
F

Frank Kabel

Hi
best suggestion: try to avoid merged cells at all :)
But if you want to detect a merged cell use something like
If Range("A1").MergeCells Then
msgbox "Cell A1 is part of a merged cell"
End If
 
G

Grey Newt

This all depends how you are referencing the column 5
If it like "Activecell.offset(0,4).value" then the merged cells will screw you up.
Think about either looking for the merged property of the cell you are on, or trying to use another way to reference the 5th column cell - maybe using "Cells(activecell.row,5).address" or something along those lines.

Perhaps post the section of code you are using to walk down and look across..
 
Top