Summarising Data using VBA

[sam987]

https://imgur.com/sD0lKf3

Hi Everyone,

I'm fairly new to VBA and i'm trying to achieve the above task. Basically i want to sort through the data such that the animals are summed to provide a total sum of a particular type of animal under a designated letter as shown. also, I only want to display the animal type if its sum is greater than zero, as shown in letters B and C summary.

I have very basic code at this point which will eventually be used to systematically sort through the data by comparing if the letter has changed from the last cell, and if it has, print the relevant information, using the SumArray variable and a range offset.

'calculate count of columns
ColCount = Range("b2", Range("b2").End(xlToRight)).Count

'calculate count of rows
RowCount = Range("a4", Range("a3").End(xlDown)).Count

ReDim SumArray(1 To 9, 1 To 1)

For i = 1 To ColCount
Array1 = Range(Range("b4").Offset(0, i - 1), Range("b4").Offset(RowCount - 1, i - 1))


For j = 1 To UBound(Array1)
SumArray(j, 1) = Array1(j, 1) + SumArray(j, 1)
Next j

Next i

I want to know if this would be the best way to go about it, because even at this point I feel as though there is a lot of reading and writing to different points involved. I also want the code to be adaptable to include additional rows/columns etc.

Thanks

 

[hiker95]

sam987,

Welcome to the MrExcel forum.

You are posting a picture. This means that if this was a problem where one needed to use your data, anyone trying to help you would have to enter the data manually.

Can we see what your actual raw data workbook/worksheets look like, and, can we see what the results (manually formatted by you) should look like?

You can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com