Percentage covered by values using VBA

[bbuk9999]

I am trying in the attached file to show the percentage in sheet (Yard Map) covered by the values in sheet (Container Yard). So when I remove one of the values in sheet (Container Yard) it does not show the percentage in the current file. For example: if I remove one of the values in sheet (Container Yard), in the related cell in sheet (Yard Map) should 80 appears. The attached file is a dynamic table with values generated in VBA. Thanks in advance.

[mdmackillop]

Add this to the Container Yard worksheet module

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim iNumPerH As Long, iNumCol As Long
Dim i As Long, k As Long
iNumPerH = Application.CountA(Range("A:A")) - 1
iNumCol = Application.CountA(Range("1:1")) - 2
With Sheets("Yard Map")
For k = 1 To iNumPerH
For i = 1 To iNumCol
.Cells(1, 2 + i).Value = i
.Cells(1 + k, 2 + i).Value = Application.WorksheetFunction.CountA(Sheets(1).Cells(1 + k, 2 + i), Sheets(1).Cells(1 + k + iNumPerH, 2 + i), Sheets(1).Cells(1 + k + 2 * iNumPerH, 2 + i), Sheets(1).Cells(1 + k + 3 * iNumPerH, 2 + i), Sheets(1).Cells(1 + k + 4 * iNumPerH, 2 + i)) / 5 * 100
Next i
Next k
End With
End Sub

[bbuk9999]

Thanks mdmackillop for the assistance, but it does not do the required when the number of the columns and rows are big for example 10 and 15. Can you make it works with big numbers as well. Thanks

[mdmackillop]

Seems to work for me. Can you post your workbook with the larger data

[bbuk9999]

When I remove one of the cell values with a big data in sheet (Container Yard), it does not show the expected percentage in sheet (Yard Map). Thanks

[mdmackillop]

I'm using your code to recreate the Yard Map table. I only added the CountA lines to determine the dimensions. I'll try to find what the issue is.

[mdmackillop]

Change this line

iNumPerH = Application.CountA(Range("A:A")) - 1
to

iNumPerH = Application.Max(Range("B:B"))

[bbuk9999]

Thanks mdmackillop , it works.