VBA Loop to skip hidden cells

[HCGaudior]

Hello,

This VBA Script references a data array on one sheet (Log) to create multiple pie charts on a different sheet (Pie Chart Generator). The script works great until it hits a hidden row. These rows need to be hidden for internal processing of the information. The crash arose when I added the .XValues to the chart. If I comment out .XValues, the script works, but I have no data labels. These are important as the number of possible titles makes the legend too large to be useful (48). The workaround is to use Data Labels.

I see two potential fixes. First, fix the .XValues script so that it does not crash when the cells are hidden. This will just create a blank chart. No big deal. Second, a loop that skips the row if the cells are hidden. I tried and failed at an if statement to do the second. At this point, I am out of my depth. This is all slightly modified scripts from various websites, but I could not find one for this function. If I could pick a solution, it would be the loop to skip hidden cells so I don't have to scroll through garbage data.

I tried to comment every part of the code (even the obvious ones). If you have questions about the code, please ask.

Sub createPieChart1x3()
'Replace chart programming (again). So Close. Crashes on hidden cells

'Delete any previous charts on sheet
Dim src As Worksheet
Set src = Worksheets("Pie Chart Generator")

Dim oChart As ChartObject

For Each oChart In src.ChartObjects
oChart.Delete

Next

'rng houses the data cells
Dim rng As Range
'cht is the frame of the pie chart
Dim cht As ChartObject
'ttl houses the category titles
Dim ttl As Range
'lbl sets the pie chart slice names
Dim lbl As Range
's houses the first row with data
Dim s As Integer
'f houses the last row with data
Dim f As Integer
'c houses the current row the entry is on
Dim c As Integer
'c starts as 0 to select s row
c = 0
'Temporary placeholder for s
s = 4
'Temporary placeholder for f
f = 7

While s + c <= f 'start + rows processed is less than end row
'Setup the data source
With Worksheets("LOG 19-20")
'.range(.Cells(s+c should be left alone. The numbers are the starting and ending columns.
Set rng = .Range(.Cells(s + c, 6), .Cells(s + c, 54))
Set ttl = .Range(.Cells(s + c, 1), .Cells(s + c, 1))
'As data labels changed, feel free to adjust (may script in the future)
Set lbl = .Range("F2:BB2")
End With

'Build the Chart object in the worksheet
Sheets("Pie Chart Generator").Select
ActiveSheet.Shapes.AddChart.Select

With ActiveChart
'Set chart type to Pie chart
.ChartType = xlPie
'Select the data holder
.SetSourceData Source:=rng
'enable the title
.HasTitle = True
'Set the title to the employees' names
.ChartTitle.Text = ttl
'Disable the legend
.HasLegend = False
'Enable labels on the pie charts
.ApplyDataLabels (xlDataLabelsShowLabelAndPercent)
'Set chart dimensions
.Parent.Top = c * 600
.Parent.Left = 400
.Parent.Height = 600
.Parent.Width = 600
'Set names of pie slices
.SeriesCollection(1).XValues = "='LOG'!$F$2:$BB$2"

End With

c = c + 1

Wend

End Sub

 

[CountTepes]

Skip the hidden rows with:

Cells(X,Y).EntireRow.Hidden will be true if the row is hidden and false if it is not.

 

[HCGaudior]

Thanks so much for your help. I took that line and turned it into this:

Do Until Worksheets("LOG").Cells(s + c, 1).EntireRow.Hidden = False
c = c + 1
Loop

It works like a champ. Now I just have to figure out how to make a statement so the graphs appear on a grid.... I'll start a new thread if I can't figure that out.

Now, how do I mark something solved to help the next person?

Skip the hidden rows with:

Cells(X,Y).EntireRow.Hidden will be true if the row is hidden and false if it is not.