If Activecell is not Merged Run Macro?

[Ste_Moore01]

Hi guys!

I've created a spreadsheet that I want to add some code to.

I have 10 columns, the first 6 have data that doesn't change. The last 4 are blank.

I want to add a macro that runs automatically when a cell is selected.

If the cell is merged with another it doesn't run a macro (in this case the cells that would be merged would be from column A to J), but if it isn't merged then it would check to see if columns G, H, I and J were empty.

If all of these cells were empty it would hide the line, if 1 of the cells wasn't empty, dependng on which cell it was, it would add the rest of the information into the empty cells.

I know this may be hard to follow so I've posted an example below.

blank.xls
	A	B	C	D	E	F	G	H	I	J	K
1	Stock Details			TRADE PRICE			Discount	INVOICE PRICE
2	Reference	Description	Pack Qty	Pack	Each/Metre	Length	%	Pack	Each/Metre	Length
3											This row is merged so it is left alone.
4	100mm x 50mm Kestrel Perimeter Trunking										This row is merged so it is left alone.
5											This row is merged so it is left alone.
6	MCT100	100mm x 50mm Kestrel Trunking	12 metres	208.20	17.35	52.05	85.00				This row has data in column G so will enter the data into H, I and J.
7	MDS100	50mm Clip-In Dividing Strip	3 metres	9.00	3.00	9.00				1.35	This row has data in column J so will enter the data into G, H, and I.
8	MCT100/IA	Internal Angle - To Suit MCT100	1	22.13	22.13	N/A			3.32		This row has data in column I so will enter the data into G, H and J
9	MCT100/EA	External Angle - To Suit MCT100	1	22.13	22.13	N/A		3.32			This row has data in column H so will enter the data into G, I and J
10	MCT100/FA	Flat Angle - To Suit MCT100	1	22.13	22.13	N/A					This row doesn't have data in G, H, I or J so will be hidden.
11	MCT100/FT	Flat Tee - To Suit MCT100	1	46.67	46.67	N/A					This row doesn't have data in G, H, I or J so will be hidden.
12	MCT100/SC	Joint Cover - To Suit MCT100	1	3.33	3.33	N/A					This row doesn't have data in G, H, I or J so will be hidden.
13	MCT100/SE	Stop End - To Suit MCT100	1	3.33	3.33	N/A					This row doesn't have data in G, H, I or J so will be hidden.
14											This row is merged so it is left alone.
DISCOUNT ENTRY

I already have the code to add the data which is

Sub test()
    Dim thisrow
    thisrow = ActiveCell.Row
    If Range("G" & thisrow)<> "" Then
        Call daddpack
        Call daddeach
        Call daddlength
    
    ElseIf Range("H" & thisrow)<> "" Then
        Call padddisc
        Call paddeach
        Call paddlength
    ElseIf Range("I" & thisrow)<> "" Then
        Call eadddisc
        Call eaddpack
        Call eaddlength
    ElseIf Range("J" & thisrow)<> "" Then
        Call ladddisc
        Call laddpack
        Call laddeach
    Else
        Rows(thisrow).Select
        Selection.EntireRow.Hidden = True
    End If
End Sub
Sub daddpack()
    Dim thisrow
    thisrow = ActiveCell.Row
    Range("h" & thisrow).Formula = "=D" & thisrow & "-((D" & thisrow & "/100)*G" & thisrow & ")"
End Sub
Sub daddeach()
    Dim thisrow
    thisrow = ActiveCell.Row
    Range("I" & thisrow).Formula = "=E" & thisrow & "-((E" & thisrow & "/100)*G" & thisrow & ")"
End Sub
Sub daddlength()
    Dim thisrow
    thisrow = ActiveCell.Row
    Range("J" & thisrow).Formula = "=IF(F" & thisrow & "=""N/A"",""N/A"",F" & thisrow & "-((F" & thisrow & "/100)*G" & thisrow & "))"
End Sub
Sub padddisc()
    Dim thisrow
    thisrow = ActiveCell.Row
    Range("G" & thisrow).Formula = "=100-((H" & thisrow & "/D" & thisrow & ")*100)"
End Sub
Sub paddeach()
    Dim thisrow
    thisrow = ActiveCell.Row
    Range("I" & thisrow).Formula = "=E" & thisrow & "-((E" & thisrow & "/100)*G" & thisrow & ")"
End Sub
Sub paddlength()
    Dim thisrow
    thisrow = ActiveCell.Row
    Range("J" & thisrow).Formula = "=if(F" & thisrow & "=""N/A"",""N/A"",F" & thisrow & "-((F" & thisrow & "/100)*G" & thisrow & "))"
End Sub
Sub eadddisc()
    Dim thisrow
    thisrow = ActiveCell.Row
    Range("G" & thisrow).Formula = "=100-((I" & thisrow & "/E" & thisrow & ")*100)"
End Sub
Sub eaddpack()
    Dim thisrow
    thisrow = ActiveCell.Row
    Range("H" & thisrow).Formula = "=D" & thisrow & "-((D" & thisrow & "/100)*G" & thisrow & ")"
End Sub
Sub eaddlength()
    Dim thisrow
    thisrow = ActiveCell.Row
    Range("J" & thisrow).Formula = "=if(F" & thisrow & "=""N/A"",""N/A"",F" & thisrow & "-((F" & thisrow & "/100)*G" & thisrow & "))"
End Sub
Sub ladddisc()
    Dim thisrow
    thisrow = ActiveCell.Row
    Range("G" & thisrow).Formula = "=100-((J" & thisrow & "/F" & thisrow & ")*100)"
End Sub
Sub laddpack()
    Dim thisrow
    thisrow = ActiveCell.Row
    Range("H" & thisrow).Formula = "=D" & thisrow & "-((D" & thisrow & "/100)*G" & thisrow & ")"
End Sub
Sub laddeach()
    Dim thisrow
    thisrow = ActiveCell.Row
    Range("I" & thisrow).Formula = "=E" & thisrow & "-((E" & thisrow & "/100)*G" & thisrow & ")"
End Sub

So is it possible to add the check to see if the cell is merged and make it run automatically?

Any help would be much appreciated. Also, if there was a way of condensing this code a little that would be hlepful too!

Thanks!

 

[Andrew Poulsom]

Try eg:

If Not Range("G" & thisrow).MergeCells Then

 

[Ste_Moore01]

Thanks very much Andrew!

That works great!