Delete columns not required
 

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 ***

Delete columns not required
 

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 ***

Delete columns not required
 

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

Thanks you

Tempy

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

Delete columns not required
 

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 ***

Delete columns not required
 

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 ***

Delete columns not required
 

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 ***

Delete columns not required
 

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 ***

Delete columns not required
 

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 ***

Delete columns not required
 

go ahead - if the most recent code does not do what you want.

--
Regards,
Tom Ogilvy

"Tempy" wrote in message
...
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 ***

Delete columns not required
 

Hi Tom, sorry it still deletes the column. Could you give me your e-mail
address please.

Tempy

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

Delete columns not required
 

--
Regards,
Tom Ogilvy

"Tempy" wrote in message
...
Hi Tom, sorry it still deletes the column. Could you give me your e-mail
address please.

Tempy

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

Delete columns not required
 

Thanks Tom, have sent it to your e-mail.


best regards,

Tempy

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