If Activecell is not Merged Run Macro?

Ste_Moore01

Active Member
Joined
Jul 13, 2005
Messages
467
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
ABCDEFGHIJK
1Stock DetailsTRADE PRICEDiscountINVOICE PRICE
2ReferenceDescriptionPack QtyPackEach/MetreLength%PackEach/MetreLength
3This row is merged so it is left alone.
4100mm x 50mm Kestrel Perimeter TrunkingThis row is merged so it is left alone.
5This row is merged so it is left alone.
6MCT100100mm x 50mm Kestrel Trunking12 metres 208.20 17.35 52.0585.00This row has data in column G so will enter the data into H, I and J.
7MDS10050mm Clip-In Dividing Strip3 metres 9.00 3.00 9.00 1.35This row has data in column J so will enter the data into G, H, and I.
8MCT100/IAInternal Angle - To Suit MCT1001 22.13 22.13N/A 3.32This row has data in column I so will enter the data into G, H and J
9MCT100/EAExternal Angle - To Suit MCT1001 22.13 22.13N/A 3.32This row has data in column H so will enter the data into G, I and J
10MCT100/FAFlat Angle - To Suit MCT1001 22.13 22.13N/AThis row doesn't have data in G, H, I or J so will be hidden.
11MCT100/FTFlat Tee - To Suit MCT1001 46.67 46.67N/AThis row doesn't have data in G, H, I or J so will be hidden.
12MCT100/SCJoint Cover - To Suit MCT1001 3.33 3.33N/AThis row doesn't have data in G, H, I or J so will be hidden.
13MCT100/SEStop End - To Suit MCT1001 3.33 3.33N/AThis row doesn't have data in G, H, I or J so will be hidden.
14This row is merged so it is left alone.
DISCOUNT ENTRY


I already have the code to add the data which is

Code:
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! :LOL:
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Watch MrExcel Video

Forum statistics

Threads
1,113,861
Messages
5,544,723
Members
410,630
Latest member
JFORTH97
Top