Hi, I have over 200 columns, I want to do a for next loop to delete the
unwanted columns, at the end I should only end with 50 columns. does anyone
have an example where I can find my columns on a collection and if is there
skip it if is not then delete entire column?

I want to avoid using a Range("A:A, D:D....").delete as there is 50 columns
I'm keeping.

Thanks,
--
Thank you...

Hi,

I'm not at all clear about what you want to do as you loop through these
cilumns but maybe this will get you going in the right direction

Sub sonic()
lastcolumn = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
For x = 1 To lastcolumn

'Do things to each column


Next
End Sub

Mike

"EXCELMACROS" wrote:

Hi, I have over 200 columns, I want to do a for next loop to delete the
unwanted columns, at the end I should only end with 50 columns. does anyone
have an example where I can find my columns on a collection and if is there
skip it if is not then delete entire column?

I want to avoid using a Range("A:A, D:D....").delete as there is 50 columns
I'm keeping.

Thanks,
--
Thank you...

I have an idea about the loop and delete columns, the problem I have is
creating the collection, I have 50 columns I want to keep, they are placed
randomly and the only thing I know is the titles, i.e. Auto, Broker, Bank...
etc.

So how the macro to look into my list of 50 titles I want to keep and then
if it won't find it then delte column.


--
Thank you...


"Mike H" wrote:

Hi,

I'm not at all clear about what you want to do as you loop through these
cilumns but maybe this will get you going in the right direction

Sub sonic()
lastcolumn = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
For x = 1 To lastcolumn

'Do things to each column


Next
End Sub

Mike

"EXCELMACROS" wrote:

Hi, I have over 200 columns, I want to do a for next loop to delete the
unwanted columns, at the end I should only end with 50 columns. does anyone
have an example where I can find my columns on a collection and if is there
skip it if is not then delete entire column?

I want to avoid using a Range("A:A, D:D....").delete as there is 50 columns
I'm keeping.

Thanks,
--
Thank you...

Where is the list of titles that you know? Did you put them in a column on a
worksheet? Or can you include that list in your code?

I think creating a worksheet (hide it later) that has the list of titles to keep
in column A.

Then I could use:

Dim myRowHeaders as range
dim myTitles as range
dim myCell as range
dim DelRng as range
dim res as variant

with worksheets("sheet with titles to keep in column A")
set mytitles = .range("a1", .cells(.rows.count,"A").end(xlup))
end with

With worksheets("some sheet that should be cleaned up")
set myrng = .range("a1",.cells(1,.columns.count).end(xltoleft) )
end with

for each mycell in myrowheaders.cells
res = application.match(mycell.value, mytitles, 0)
if iserror(res) then
'no match, so delete it
if delrng is nothing then
set delrng = mycell
else
set delrng = union(delrng,mycell)
end if
end if
next mycell

if delrng is nothing then
'keep everything
else
delrng.entirecolumn.delete
end if

======
untested, uncompiled. Watch for typos.

EXCELMACROS wrote:

I have an idea about the loop and delete columns, the problem I have is
creating the collection, I have 50 columns I want to keep, they are placed
randomly and the only thing I know is the titles, i.e. Auto, Broker, Bank...
etc.

So how the macro to look into my list of 50 titles I want to keep and then
if it won't find it then delte column.

--
Thank you...

"Mike H" wrote:

Hi,

I'm not at all clear about what you want to do as you loop through these
cilumns but maybe this will get you going in the right direction

Sub sonic()
lastcolumn = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
For x = 1 To lastcolumn

'Do things to each column


Next
End Sub

Mike

"EXCELMACROS" wrote:

Hi, I have over 200 columns, I want to do a for next loop to delete the
unwanted columns, at the end I should only end with 50 columns. does anyone
have an example where I can find my columns on a collection and if is there
skip it if is not then delete entire column?

I want to avoid using a Range("A:A, D:D....").delete as there is 50 columns
I'm keeping.

Thanks,
--
Thank you...

--

Dave Peterson

Hi,

So you want to loop through each column and delete columns that don't have
the hedaers in your collection. Try this

You will seee MyArray at the start. Note the syntax and change this to
include all the headers you want to KEEP. It looks for these in row 1 and
deletes any column taht doesn't have them

Sub sonic()
Dim i As Long, delflag as Boolean
MyArray = Array("aaa", "bbb", "cccc", "dddd") 'your headers names
lastcolumn = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
For x = lastcolumn To 1 Step -1
delflag = True
For i = LBound(MyArray) To UBound(MyArray)
If Cells(1, x).Value = MyArray(i) Then
delflag = False
Exit For
End If
Next
If delflag Then
Cells(1, x).EntireColumn.Delete
End If
Next
End Sub

Mike

"EXCELMACROS" wrote:

I have an idea about the loop and delete columns, the problem I have is
creating the collection, I have 50 columns I want to keep, they are placed
randomly and the only thing I know is the titles, i.e. Auto, Broker, Bank...
etc.

So how the macro to look into my list of 50 titles I want to keep and then
if it won't find it then delte column.


--
Thank you...


"Mike H" wrote:

Hi,

I'm not at all clear about what you want to do as you loop through these
cilumns but maybe this will get you going in the right direction

Sub sonic()
lastcolumn = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
For x = 1 To lastcolumn

'Do things to each column


Next
End Sub

Mike

"EXCELMACROS" wrote:

Hi, I have over 200 columns, I want to do a for next loop to delete the
unwanted columns, at the end I should only end with 50 columns. does anyone
have an example where I can find my columns on a collection and if is there
skip it if is not then delete entire column?

I want to avoid using a Range("A:A, D:D....").delete as there is 50 columns
I'm keeping.

Thanks,
--
Thank you...

This is AWESOME, thank you a lot, it worked.
--
Thank you...


"Mike H" wrote:

Hi,

So you want to loop through each column and delete columns that don't have
the hedaers in your collection. Try this

You will seee MyArray at the start. Note the syntax and change this to
include all the headers you want to KEEP. It looks for these in row 1 and
deletes any column taht doesn't have them

Sub sonic()
Dim i As Long, delflag as Boolean
MyArray = Array("aaa", "bbb", "cccc", "dddd") 'your headers names
lastcolumn = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
For x = lastcolumn To 1 Step -1
delflag = True
For i = LBound(MyArray) To UBound(MyArray)
If Cells(1, x).Value = MyArray(i) Then
delflag = False
Exit For
End If
Next
If delflag Then
Cells(1, x).EntireColumn.Delete
End If
Next
End Sub

Mike

"EXCELMACROS" wrote:

I have an idea about the loop and delete columns, the problem I have is
creating the collection, I have 50 columns I want to keep, they are placed
randomly and the only thing I know is the titles, i.e. Auto, Broker, Bank...
etc.

So how the macro to look into my list of 50 titles I want to keep and then
if it won't find it then delte column.


--
Thank you...


"Mike H" wrote:

Hi,

I'm not at all clear about what you want to do as you loop through these
cilumns but maybe this will get you going in the right direction

Sub sonic()
lastcolumn = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
For x = 1 To lastcolumn

'Do things to each column


Next
End Sub

Mike

"EXCELMACROS" wrote:

Hi, I have over 200 columns, I want to do a for next loop to delete the
unwanted columns, at the end I should only end with 50 columns. does anyone
have an example where I can find my columns on a collection and if is there
skip it if is not then delete entire column?

I want to avoid using a Range("A:A, D:D....").delete as there is 50 columns
I'm keeping.

Thanks,
--
Thank you...