Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: How to find number of chart's in one worksheet

  1. #1
    New Member
    Join Date
    May 2002
    Location
    India
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have to find all the Chart's that are present in the excel sheet .
    Please help me.

    [ This Message was edited by: asramasarma on 2002-05-03 05:04 ]

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Sheffield, UK
    Posts
    253
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    There is probably a better way, but this was my solution:

    Sub ChrsCount()
    'Find last cell on sheet
    Selection.SpecialCells(xlCellTypeLastCell).Select
    lastcol = Selection.Column
    lastrow = Selection.Row
    Range("A1").Select

    'Count characters in each cell
    For x = 1 To lastcol
    For y = 1 To lastrow
    cellcount = Len(Cells(y, x).Value)
    mytotal = mytotal + cellcount
    Next y
    Next x
    MsgBox ("Total Characters = " & mytotal)

    End Sub

    Phil.

  3. #3
    Board Regular
    Join Date
    May 2002
    Location
    Ipswich, Suffolk, England
    Posts
    136
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    a quick way without the macro is to select the whole sheet containing the data and copy and paste in MS word. Then use the word count which also give the number od characters

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Sheffield, UK
    Posts
    253
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry, Is that a joke, or is there really something called cahrts? Am I being really slow?

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Sheffield, UK
    Posts
    253
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    OK, Charts. Thats much easier. One line of VB should do it:

    myCount = ActiveSheet.ChartObjects.Count

    You realise that changing your original entry makes my original answer look very bizarre!

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The following will do it for the whole workbook (you could also make this a function, I suppose): -

    Public Sub NoOfCharts()

    Dim ch As ChartObject
    Dim ws As Worksheet
    Dim x As Integer
    Dim y As Integer

    y = ThisWorkbook.Charts.Count

    For Each ws In Worksheets
    For Each ch In ws.ChartObjects
    x = x + 1
    Next ch
    Next ws

    MsgBox x & " embedded charts." & vbCrLf & y & " chart sheets."

    End Sub

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •