I have a matrix file with 83 comlumns and 21,000+ employees
In column A returns the employee ID#
Column B returns Column headers
Column C Returns the Value in the field
field values of : Complete; "NR"; or NULL.
---------------------------------------------------------------------------
<TBODY>
</TBODY>
----------------------------------------------------------------------------
RESULTS from VB code below
<TBODY>
</TBODY>----------------------------------------------------------------------------
VB Script I found on the web
Problem is: The script exceeds excels 1 million rows -
How can the VB Code be adjusted to exlude the "NR" values on the new worksheet?
In column A returns the employee ID#
Column B returns Column headers
Column C Returns the Value in the field
field values of : Complete; "NR"; or NULL.
---------------------------------------------------------------------------
| Employee Id</SPAN> | Item 1</SPAN> | Item 2</SPAN> | Item 3</SPAN> | Item 4</SPAN> | Item 5</SPAN> | Item 6</SPAN> | Item 7</SPAN> | Item 8</SPAN> | Item 9</SPAN> | Item 10</SPAN> | Item 11</SPAN> | Item 12</SPAN> |
| 1555</SPAN> | NR</SPAN> | NR</SPAN> | NR</SPAN> | NR</SPAN> | NR</SPAN> | NR</SPAN> | Complete</SPAN> | NR</SPAN> | NR</SPAN> | NR</SPAN> | NR</SPAN> | NR</SPAN> |
| 1563</SPAN> | NR</SPAN> | NR</SPAN> | Complete</SPAN> | NR</SPAN> | Complete</SPAN> | Complete</SPAN> | NR</SPAN> | NR</SPAN> | NR</SPAN> | NR</SPAN> | NR</SPAN> | |
| 1652</SPAN> | NR</SPAN> | NR</SPAN> | NR</SPAN> | NR</SPAN> | NR</SPAN> | NR</SPAN> | NR</SPAN> | NR</SPAN> | NR</SPAN> | NR</SPAN> | ||
| 1688</SPAN> | NR</SPAN> | NR</SPAN> | NR</SPAN> | Complete</SPAN> | Complete</SPAN> | NR</SPAN> | NR</SPAN> | Complete</SPAN> | Complete</SPAN> | |||
| 1708</SPAN> | NR</SPAN> | NR</SPAN> | NR</SPAN> | NR</SPAN> | NR</SPAN> | NR</SPAN> | NR</SPAN> | NR</SPAN> | NR</SPAN> | NR</SPAN> | NR</SPAN> | NR</SPAN> |
| 1734</SPAN> | NR</SPAN> | NR</SPAN> | NR</SPAN> | NR</SPAN> | NR</SPAN> | NR</SPAN> | NR</SPAN> | |||||
| 1809</SPAN> | NR</SPAN> | NR</SPAN> | Complete</SPAN> | Complete</SPAN> | NR</SPAN> | Complete</SPAN> | Complete</SPAN> | Complete</SPAN> | NR</SPAN> | NR</SPAN> |
<TBODY>
</TBODY>
----------------------------------------------------------------------------
RESULTS from VB code below
| Row | Column | Value |
| 1555 | Item 1 | NR |
| 1555 | Item 2 | NR |
| 1555 | Item 3 | NR |
| 1555 | Item 4 | NR |
| 1555 | Item 5 | NR |
| 1555 | Item 6 | NR |
| 1555 | Item 7 | Complete |
| 1555 | Item 8 | NR |
| 1555 | Item 9 | NR |
| 1555 | Item 10 | NR |
| 1555 | Item 11 | NR |
| 1555 | Item 12 | NR |
| 1563 | Item 1 | NR |
| 1563 | Item 2 | NR |
| 1563 | Item 3 | Complete |
| 1563 | Item 4 | NR |
| 1563 | Item 5 | Complete |
| 1563 | Item 6 | Complete |
| 1563 | Item 7 | NR |
| 1563 | Item 8 | NR |
| 1563 | Item 9 | NR |
| 1563 | Item 10 | |
| 1563 | Item 11 | NR |
| 1563 | Item 12 | NR |
| 1652 | Item 1 | NR |
<TBODY>
</TBODY>
VB Script I found on the web
Code:
Sub NormaliseTable()
' start with the cursor in the table
Dim rTab As Range, C As Range, rNext As Range
Set rTab = ActiveCell.CurrentRegion
If rTab.Rows.Count=1 Or rTab.Columns.Count = 1 Then
MsgBox "Not a well-formed table!"
Exit Sub
End If
Worksheets.Add ' the sheet for the results
Range("A1:C1") = Array("Row","Column","Value")
Set rNext = Range("A2")
For Each C In rTab.Offset(1,1).Resize(rTab.Rows.Count-1, _
rTab.Columns.Count-1).Cells
If Not IsEmpty(C.Value) Then
rNext.Value = rTab.Cells(C.Row-rTab.Row+1,1)
rNext.Offset(0,1).Value = rTab.Cells(1,C.Column-rTab.Column+1)
rNext.Offset(0,2).Value = C.Value
Set rNext = rNext.Offset(1,0)
End If
Next
End SubProblem is: The script exceeds excels 1 million rows -
How can the VB Code be adjusted to exlude the "NR" values on the new worksheet?
