PDA

View Full Version : Array help, deleting columns.



mzsuga
10-21-2009, 09:34 AM
I have the following macro which looks for the column name and deletes columns that are not in the Array List. The problem I have is that in the sheet that I am working off from, some of the column name is weird. It looks like this:

Price
(per thousand)

as oppose to Price(per thousand)

I think it is written that way to so that the per thousand part will show underneathe the Price. So with my macro it has to be exact spelling, but I don't know how to reflect that extra line in the macro. When I copy and paste it, it won't work. Please help.



cStay = Array("Name", "Address", "Zip (Within Select)", "Price
(per thousand)")
For Each rCell In Range(Cells(4, 1), _
Cells(4, Columns.Count).End(xlToLeft))
With rCell
For i = 0 To UBound(cStay)
If .Value = cStay(i) Then
bStay = True
Exit For
End If
Next i
If Not bStay Then
If rDel Is Nothing Then
Set rDel = .Cells
Else
Set rDel = Union(rDel, .Cells)
End If
Else
bStay = False
End If
End With
Next rCell
If Not rDel Is Nothing Then rDel.EntireColumn.Delete
End If
End Sub

xld
10-21-2009, 09:48 AM
cStay = Array("Name", "Address", "Zip (Within Select)", "Price " & Chr(10) & "(per thousand)")

mzsuga
10-21-2009, 09:50 AM
cStay = Array("Name", "Address", "Zip (Within Select)", "Price " & Chr(10) & "(per thousand)")


thank you, that worked perfect!

mdmackillop
10-21-2009, 12:30 PM
Hi mszuga,
Use the green VBA button to format your code. "Code" tags don't work here.