Macro to delete columns if they contain data - need help creating

[heweaver]

I am working with Excel 2003. I have a series of spreadsheets generated by my companies database program. For a special project I am trying to work on, I would be able to get my results much much quicker if I could get a macro that would do the following:

The first row is a list of headers. I need the macro to look at each cell below the header, and if any data exists in that column, I need it to delete the entire column. The results I will get will tell me quickly which headers are in certain jobs but are constantly not being used. They are trimming the database at work and this type of macro would help me tremendously.

I figured this may be a quick simple macro for any one of you who has experience programming. Unfortunately I have no excel training in macros (yet) so any help is appreciated!

Thanks in advance!

[stanleydgromjr]

heweaver,

Welcome to the ExcelTip Board.

Please attach a copy of your workbook - scroll down and see "Manage Attachments".


Have a great day,
Stan

[rylo]

Hi

Try this.

Please Login or Register  to view this content.

rylo

[heweaver]

Wow I was just looking for a sample workbook to upload when I saw the next post.

Thanks Rylo, that works great and does exactly what I asked!

I thought of one more possibly useful feature if its not too much more trouble for someone to add a line... is it possible to not delete the first (X) number of columns...either 4 or 6, which would be columns D or F depending ... but maybe a piece of code where I could change the number myself if needed. This way, with my worksheets, it would give me an idea of the number of jobs and a few other pieces of info thats always in the first few columns. I will attach a workbook if needed, but this may also be a simple addition for someone. I will have to get on VPN to get to the workbook if needed, but if so just let me know.

Thanks!

[rylo]

Hi

How about

Please Login or Register  to view this content.

Put in the number of columns from the left you want to keep 4 or 6...

It doesn't do any testing - just relies on the user providing valid input.

rylo

[heweaver]

Where does the four or six go? I had trouble telling from your last post...sorry

[rylo]

Hi

run the code, and an input box pops up and asks you a question. Put in the number and keep going...


rylo

[heweaver]

This works, one thing though, if I type in 6 it will only keep 5 columns, so I will have to type in 7 to have it keep 6 with the code as-is... I can live with that, but I didn't know if you knew it was supposed to be doing that or not. Thanks!

Hi

How about

Please Login or Register  to view this content.

Put in the number of columns from the left you want to keep 4 or 6...

It doesn't do any testing - just relies on the user providing valid input.

rylo

[rylo]

Hi

my bad. Try

Please Login or Register  to view this content.

rylo

[heweaver]

I will try that in a few when I get a chance. And would I be correct to assume that if I replace the following piece of your code:

For i = Cells(1, Columns.Count).End(xlToLeft).Column To InputBox("How many columns do you want to keep")+1 Step -1

with the following code:

For i = Cells(1, Columns.Count).End(xlToLeft).Column To 6+1 Step -1

that it would auto keep the first six columns without presenting any dialog boxes? (I tried to underline the changes between the two).

But I think once I know that for sure, that may finish up this project! I am sure I will be back soon though for help with another similar project.

This is a great forum and I really appreciate all the help!

[rylo]

Hi

Sure would but why not just put in a 7?

Please Login or Register  to view this content.

Again, it does assume that there will always be at least 7 columns of items in the first row...


rylo

[heweaver]

I see... 6+1, 7, 13-5, or anything like that would give me what I want. I may keep 6+1 so its easier for me (or others after me) to see that the 6 is what its keeping... I understand it but if someone looked at it later, the 7 may throw them off.

Thanks again and I will be back here soon, that's for sure!

[rylo]

Hi

OK, and I'm glad you will be back. I'll mark this post as solved.

rylo