VBA for Organizational Chart (Filtered)

Hi!

Can you help me with this code?

Sub MakeHierarchy()
'
'
Dim shpHolder As Shape
Dim objMain As SmartArt
Dim objNode As SmartArtNode
Dim rngData As Range
Dim lngRow As Long

Set rngData = Range("A2:E1000")

Set shpHolder = ActiveSheet.Shapes.AddSmartArt(Application.SmartArtLayouts( _
"urn:microsoft.com/office/officeart/2005/8/layout/orgChart1"))

Set objMain = shpHolder.SmartArt

'clear default nodes
Do While objMain.AllNodes.Count > 1
objMain.AllNodes(objMain.AllNodes.Count).Delete
Loop

lngRow = 1
Set objNode = objMain.AllNodes(1)
objNode.TextFrame2.TextRange.Text = rngData.Cells(lngRow, 3)


For lngRow = 2 To rngData.Rows.Count
lngParentIndex = Application.WorksheetFunction.Match(rngData.Cells(lngRow, 2), rngData.Columns(1), 0)
Set objNode = objMain.AllNodes(lngParentIndex).AddNode(msoSmartArtNodeBelow, msoSmartArtNodeTypeDefault)
objNode.TextFrame2.TextRange.Text = rngData.Cells(lngRow, 3)
Next

End Sub
Sub Macro2()
'
' Macro2 Macro
'

'
Call ActiveSheet.Shapes.AddSmartArt(Application.SmartArtLayouts( _
"urn:microsoft.com/office/officeart/2005/8/layout/orgChart1")).Select
End Sub



for the range... i can't select the filtered section of the chart. do you know the code for me to just select the visible data?
I have just got that code here. hope you can help me with this one.
Also do you know how to make it a button?

Thank you so much in advance...

Hi,

Thank you for the info.

How can I use that for the range?

Set rngData = Range("A2:E1000")

If only visible data would I use, how can I set my rngData?

Thank you so much in advance

Thank you...

I've tried it with the data I've had but it seems like the data was not all captured.

rank link to rank name region
a a 1
b a b 2
c b c 3
d b d 2
e d e 2

this is the sample data...

if i filtered 1 and 2,
just ''a"' and "b" would appear.

How will I format the range so that a,b,d and e would be shown in the graph?

Thank you.

Can you send through a sample sheet with the graph?

Here's the sample data.

sample data.xlsx

Also can it be possible to program the background color of the charts depending on their positions/regions?

Thank you very much for the help.

Hmm, not sure!?!?
I haven't played around much with smart art before. I know that people have been having problems linking smart art to cells.
The visible cells code is working as per the attached... maybe you can copy the filtered data into a new sheet first?
Visible Cells Example.xlsm

Thanks for this spitfireblue. but how can i make it into a hierarchy? do you know a code?

Sorry for a lot of queries.

I need a lot of macros to be implemented. subtotal with different criterias, automatic chart and so on.

Thanks for your response appreciate it much.

Maybe check this out...

http://answers.microsoft.com/en-us/o...27?db=5&auth=1

Hi,

I've tried looking at it, but it seems that it wasn't the info i've been seeking for. but anyway thank you for this,