VBA Loop to skip hidden cells

HCGaudior

New Member
Joined
Mar 17, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

CountTepes

Active Member
Joined
Nov 8, 2010
Messages
252
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

New Member
Joined
Mar 17, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,356
Messages
5,595,681
Members
414,009
Latest member
SNesbyCarr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top