I am trying to remove empty elements from a Collection. Here is the code I have so far.
From the Locals window here is what Altered looks like:
Altered
I would like to remove all elements of Item 1(4) so I can paste the collection back into Range(Cells(12, 2), Cells(19, 12))
My Table looks like this
Start:
<tbody>
</tbody>
I want it to look like this when done:
<tbody>
</tbody>
Any help is appreciated!
Code:
Sub T()
Dim i As Integer
Dim Altered As New Collection
Altered.Add Range(Cells(12, 2), Cells(19, 12)).FormulaR1C1
For i = 1 To Altered.Count
If Altered.Item(i) = "" Then
Altered.Remove i
End If
Next i
End Sub
From the Locals window here is what Altered looks like:
Altered
Item 1(1)
Item 1(2)
Item 1(3)
Item 1(4)
Item 1(2)
Item 1(3)
Item 1(4)
Item 1(4,1) ""
Item 1(4,2) ""
Item 1(4,3) ""
Item 1(4,4) ""
Item 1(4,5) ""
Item 1(4,6) ""
Item 1(4,7) ""
Item 1(4,8) ""
Item 1(4,9) ""
Item 1(4,10) ""
Item 1(4,11) ""
Item 1(4,2) ""
Item 1(4,3) ""
Item 1(4,4) ""
Item 1(4,5) ""
Item 1(4,6) ""
Item 1(4,7) ""
Item 1(4,8) ""
Item 1(4,9) ""
Item 1(4,10) ""
Item 1(4,11) ""
Item 1(5)
Item 1(6)
Item 1(7)
Item 1(8)
Item 1(9)
Item 1(10)
Item 1(11)
Item 1(6)
Item 1(7)
Item 1(8)
Item 1(9)
Item 1(10)
Item 1(11)
I would like to remove all elements of Item 1(4) so I can paste the collection back into Range(Cells(12, 2), Cells(19, 12))
My Table looks like this
Start:
A | B | C | D | E | F | G | H | I | J | K | L |
12 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
13 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
14 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
15 | |||||||||||
16 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
17 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
18 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
19 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
<tbody>
</tbody>
I want it to look like this when done:
A | B | C | D | E | F | G | H | I | J | K | L |
12 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
13 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
14 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
15 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
16 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
17 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
18 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
19 |
<tbody>
</tbody>
Any help is appreciated!