Hello all, i need some help desperately please !! I am in South Africa
and a spreadsheet gets created in Germany which i download on a daily
basis and manipulate to use in Access. My problem is that they, the
Germans, change the spreadsheet by putting new columns in evry now and
then and then i have to modify my code which takes hours !!

Could i possibly look for the headings that i require, approx. 96
columns, and delete the rest ?

If this is possible or any suggestions i would eternally be gratefull
for some code or suggestions.


Tempy

*** Sent via Developersdex http://www.developersdex.com ***

Sub Processcolumns()
Dim vList as Variant, lastCol as Long
Dim i as Long, res as Variant
vList = Array("Last","First","Middle","Street", _
"City","District","Country", . . . _
"Total","Cat1") 'list a total of 96 column headings found in row 1
lastCol = Cells(1,256).End(xltoLeft).Column
if LastCol = 96 then exit sub
for i = LastCol to 1 step -1
res = Application.Match(cells(1,i),vList,0)
if iserror(res) then
columns(i).Delete
end if
Next
End Sub

--
Regards,
Tom Ogilvy

"Tempy" wrote in message
...
Hello all, i need some help desperately please !! I am in South Africa
and a spreadsheet gets created in Germany which i download on a daily
basis and manipulate to use in Access. My problem is that they, the
Germans, change the spreadsheet by putting new columns in evry now and
then and then i have to modify my code which takes hours !!

Could i possibly look for the headings that i require, approx. 96
columns, and delete the rest ?

If this is possible or any suggestions i would eternally be gratefull
for some code or suggestions.


Tempy

*** Sent via Developersdex http://www.developersdex.com ***

Hi Tom, thanks so much for help. As always it is appreciated.

Thanks you

Tempy

*** Sent via Developersdex http://www.developersdex.com ***

Hi Tom, I'm sorry, i am not a trained programmer and just want to ask if
the code you gave me is the best way to go or is there another way that
you would suggest ?

regards,

Tempy

*** Sent via Developersdex http://www.developersdex.com ***

My answer was based on the information you provided. I don't know if it is
the best for you or not. What is your concern?

--
Regards,
Tom Ogilvy

"Tempy" wrote in message
...
Hi Tom, I'm sorry, i am not a trained programmer and just want to ask if
the code you gave me is the best way to go or is there another way that
you would suggest ?

regards,

Tempy

*** Sent via Developersdex http://www.developersdex.com ***

Sorry, just my mind working overtime, i got some code from the site, i
cant remember who, which saves the names of the different toolbars open
on my spreadsheet in my Macro document and then re-installs them when i
am finished and just wondered if i could have the heading names listed
on sheet1 and then the code looks at the list for thew names required?
I have just tried your code and it deleted the column, however i think
it is the format of the heading it is.. - " Snr Part" and it is wrapped
with the "Snr" above the "Part".
When i look at the format it is text and has a little square after "Snr"
which i asume it is to tell it a space and wrap ?

Tempy

*** Sent via Developersdex http://www.developersdex.com ***

It sounds like you have some non printing characters in the column. The
code as written looks for an exact match, so you can modify it for a partial
match with

Sub Processcolumns()
Dim vList as Variant, lastCol as Long
Dim i as Long, res as Variant
vList = Array("Last","First","Middle","Street", _
"City","District","Country", . . . _
"Total","Cat1") 'list a total of 96 column headings found in row 1
lastCol = Cells(1,256).End(xltoLeft).Column
if LastCol = 96 then exit sub
for i = LastCol to 1 step -1
res = Application.Match("*" & cells(1,i) & "*",vList,0)
if iserror(res) then
columns(i).Delete
end if
Next
End Sub

this might help.

If you wanted to list the column names in a sheet rather than in the array,
let's assume it is in a sheet named List in the workbook containing the
code:

Sub Processcolumns()
Dim vList as Variant, lastCol as Long
Dim i as Long, res as Variant
vList = ThisWorkbooks.Worksheets("List") _
.Range("A1:A96").Value
lastCol = Cells(1,256).End(xltoLeft).Column
if LastCol = 96 then exit sub
for i = LastCol to 1 step -1
res = Application.Match("*" & cells(1,i) & "*",vList,0)
if iserror(res) then
columns(i).Delete
end if
Next
End Sub

You would also have the names listed in row1 as column headings
vList = ThisWorkbooks.Worksheets("List") _
.Range("A1:CR1").Value


--
Regards,
Tom Ogilvy


--
Regards,
Tom Ogilvy

"Tempy" wrote in message
...
Sorry, just my mind working overtime, i got some code from the site, i
cant remember who, which saves the names of the different toolbars open
on my spreadsheet in my Macro document and then re-installs them when i
am finished and just wondered if i could have the heading names listed
on sheet1 and then the code looks at the list for thew names required?
I have just tried your code and it deleted the column, however i think
it is the format of the heading it is.. - " Snr Part" and it is wrapped
with the "Snr" above the "Part".
When i look at the format it is text and has a little square after "Snr"
which i asume it is to tell it a space and wrap ?

Tempy

*** Sent via Developersdex http://www.developersdex.com ***

Hello Tom,I have an exmple of the spreadsheet that i can send to you if
you would like me to.

regards,

Tempy

*** Sent via Developersdex http://www.developersdex.com ***