Good Morning Forums,
First post here, so hopefully the community can help me out! I am trying to generate a column graph that doesn't have gaps in it when data is not present. I assume I may need to use macros, so I need some assistance as I have only just started learning VBA.
For this column graph, I have a "Sample Number" for each row, and within each sample there could be a number of substances from 0 (none) to 30 (substance IDs 1-30 represent each of the columns for any given sample/row). Since any one sample could have any number of the 30+ substances, I am trying to generate a graph that only shows those substances which are present; essentially I am trying to create a "positive only" column graph.
Now, I could go in and delete each substance name manually, but the issue is the highlighted cells for that graph are still there. So, even if I delete the headers (substance name) for each sample, then what I'm left with is a gap in the column graph.
So what I am thinking is I need a way for this macro (or if anyone has some other ideas) that will check to see if a sample is less than a certain cutoff, and if it is, not show up as a zero, or a gap. This may require that the sample headers row be completely deleted in order for the cell to not show (I really am not sure, which is why I am here.)
The other glaring issue is that multiple samples are associated with each substance name, and therefore deleting the row for one sample (with not data) could cause deletion of the data in a row for a sample where that substance is present.
For example, let's say I have 3 samples, and within each of these samples there are 2 substances. The substances in sample 1 are A and B; sample 2 are A and C; sample 3 are B and C. That means that in samples 1-3, there are no traces of D-Z etc. (since I have 30 substances) so I would want the D-Zs' names to not show up in the graph. Additionally, the problem I mentioned before: Sample 1 has A, but sample 3 does not... I can't delete the header associated with both of these then (I have attached an image to help).
I know this is probably pretty complicated, so please don't hesitate to ask for clarifications. Maybe I ought to learn python or something (haaha). If there is a way to create a macro for it, that would definitely increase the efficiency of this whole process.
