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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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
202
ipbr21054
I
N
  • Question Question
VBA to remove all email addresses from cells
Replies
1
Views
430
AlphaFrog
AlphaFrog
JenniferMurphy
  • Question Question
Worksheet expression to select items from a list using weighted random numbers
Replies
2
Views
500
JenniferMurphy
JenniferMurphy
Sufiyan97
  • Solved
VBA Filter Names from List One by One and then Copy filtered data v2
Replies
6
Views
177
Sufiyan97
Sufiyan97
S
  • Solved
Rotating Data from Horizontal to Vertical using VBA Code
Replies
3
Views
385
kevin9999
K

Forum statistics

Threads
1,223,897
Messages
6,175,269
Members
452,628
Latest member
dd2

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