Removing Elements from Collection

S

smpatty08

Board Regular
Joined
May 16, 2014
Messages
155
I am trying to remove empty elements from a Collection. Here is the code I have so far.

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(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(5)
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:
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

I want it to look like this when done:
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Any help is appreciated!
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
I would not use a collection for that purpose. And your goal is not clear.
Do you want to remove each cell that is empty
or each row where every cell in the row is empty
or each row where any of the cells in that row are empty.

One quick approach might be
Code: 
Range(Cells(12, 2), Cells(19, 12)).SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp
 
Upvote 0
mikerickson said:
I would not use a collection for that purpose. And your goal is not clear.
Do you want to remove each cell that is empty
or each row where every cell in the row is empty
or each row where any of the cells in that row are empty.

One quick approach might be
Code: 
Range(Cells(12, 2), Cells(19, 12)).SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp
Click to expand...

I would like to arrange the rows that contain data to make them contiguous and put all the blanks at the bottom. I would rather not delete the row completely because it would mess up some formatting elsewhere is the worksheet.
 
Upvote 0
Code: 
Dim inArray as Variant, outArray() as String
Dim i as Long, j as Long, k as Long, m as Long

inArray = Range(Cells(12, 2), Cells(19, 12)).FormulaR1C1

ReDim outArray(1 to UBound(inArray,1), 1 to UBound(inArray, 2))

k = 1: m = 1
For i = 1 to UBound(inArray,1)
    For j = 1 to UBound(inArray, 2)
        If inArray(i, j) <> "" then
            outArray(k, m) = inArray(i, j)
            m = m+1: If UBound(outArray,2) < m Then k = k+1: m=1
        End If
    Next j
Next i

Range(Cells(12, 2), Cells(19, 12)).FormulaR1C1 = outArray
 
Upvote 0
mikerickson said:
Code: 
Dim inArray as Variant, outArray() as String
Dim i as Long, j as Long, k as Long, m as Long

inArray = Range(Cells(12, 2), Cells(19, 12)).FormulaR1C1

ReDim outArray(1 to UBound(inArray,1), 1 to UBound(inArray, 2))

k = 1: m = 1
For i = 1 to UBound(inArray,1)
    For j = 1 to UBound(inArray, 2)
        If inArray(i, j) <> "" then
            outArray(k, m) = inArray(i, j)
            m = m+1: If UBound(outArray,2) < m Then k = k+1: m=1
        End If
    Next j
Next i

Range(Cells(12, 2), Cells(19, 12)).FormulaR1C1 = outArray
Click to expand...

After I updated
Code: 
outArray() as String
To
Code: 
outArray as Variant
It worked perfectly
This is exactly what I needed! Thank You!
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question Question
Advice to remove old code for a new code
Replies
2
Views
192
ipbr21054
I
N
  • Question Question
VBA to remove all email addresses from cells
Replies
1
Views
405
AlphaFrog
AlphaFrog
JenniferMurphy
  • Question Question
Worksheet expression to select items from a list using weighted random numbers
Replies
2
Views
493
JenniferMurphy
JenniferMurphy
Sufiyan97
  • Solved
VBA Filter Names from List One by One and then Copy filtered data v2
Replies
6
Views
174
Sufiyan97
Sufiyan97
S
  • Solved
Rotating Data from Horizontal to Vertical using VBA Code
Replies
3
Views
380
kevin9999
K

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top