PDA

View Full Version : How to find available rows, columns and bits from matrix definition in spreadsheet



sjeter
04-29-2015, 09:37 PM
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
04-30-2015, 01:41 PM
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