How to find available rows, columns and bits from matrix definition in spreadsheet

[sjeter]

In an Excel Spreadsheet, I am defining data to be output into a 4 column matrix with 512 rows.
Each block of of the matrix can hold 12 individual bits of data

	column1	column2	column 3	column 4
row 1	12 bits	12 bits	12 bits	12 bits
row 2	12 bits	12 bits	12 bits	12 bits
row 3	12 bits	12 bits	12 bits	12 bits
row 4	12 bits	12 bits	12 bits	12 bits
..
..
row 512	12 bits	12 bits	12 bits	12 bits

In the Excel Spreadsheet, I have already assigned many rows and bits of data similar to the layout below.
Now, I am needing a macro that I could run to find available bits, columns, or rows.


For example, in the definition below, visually it is apparent that rows 1, 2 and 3 are not yet defined so they would be available.
In row 8, columns 2 & 4 would still be available, and so on.
I am needing a macro to do this so I do not have to visually search as I start to fill in more of the definition.

Row	Columns	Start Bit	Stop Bit
4	all	1	7
8	1, 3	1	12
11	all	6	12
18	all	1	12
98	all	3	4
234	all	1	5
345	all	1	12
423	1, 3	1	12

[Kenneth Hobs]

It is not apparent to me. I don't know if you are working from the first example range or the 2nd. Attaching a short example file is usually the best way to explain a problem and get help. Click the paperclip icon in a reply after clicking the Go Advanced button in lower right to browse and attach a file.

If it is just a matter of getting the blank cells, something like this can be used. I just tested a 4x4 range. Of course one can iterate the full range by block size as needed.

If you are not familiar with the Immediate Window in the Visual Basic Editor, VBE, one can send output there using Debug.Print and do short tests there as well.

Sub test_BlanksRrange()  Dim r As Range, s As String
  Set r = ActiveSheet.Range("A1:D4")
  s = BlanksRange(r).Address(external:=True)
  Debug.Print s
End Sub


Function BlanksRange(aSet As Range) As Range
  Dim c As Range, r As Range, ws As Worksheet
  Set ws = aSet.Parent
  On Error Resume Next
  For Each c In aSet
    If c.Value2 = "" Then
      If r Is Nothing Then
        Set r = c
        Else
        Set r = Union(r, c)
      End If
    End If
  Next c
  Set BlanksRange = r
End Function