Delete Columns where Row 1 is not equal to X or Y

[dhpeter83]

Hello,

I have a macro that deletes unwanted columns from a raw extract of data. The problem is that the columns order will be random at every extract so a commonly recoreded macro will not work.

I want to delete any columns where
Type
TenderType
AccountToken
AccountName
Activity
CountDistinct

is NOT in ROW 1.

So essentially i will be left with the column above for this specific page.

[berlan]

Hi dhpeter83, try this:

Please Login or Register  to view this content.

Kind regards,
berlan

[dhpeter83]

It didn't work. It kept column "type" but no others. If it's helpful, "type" was the first column in the data set that matched the requirements.

[pjwhitfield]

hmmm I tested berlans code and it worked fine for me. Heres my alternative code but its in no way as good as berlans.

Please Login or Register  to view this content.

[berlan]

Hi again, in fact it worked for me (in Excel 2013). Otherwise, here's another option:

Please Login or Register  to view this content.

Cheers, berlan

[dhpeter83]

Yeah I don't know why, it reads as thought it should work, but it's only retaining the type column.. I'm on 2007

The newer macro froze my excel. Thanks for helping me with this

[jindon]

Try this with a back up file.

Please Login or Register  to view this content.

[dhpeter83]

This latest one worked like a charm! Thanks for your help!

[jindon]

You are welcome and thanks for the rep.


If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[dhpeter83]

I have a follow up question for your brilliant minds.

These columns may not always be in the same order...

How do I find the column, say, account number, and select it to apply some formatting to it, or cut and paste it to column b from where ever it may be,

[jindon]

See if this is how you wanted.

Please Login or Register  to view this content.

[dhpeter83]

Great! This works perfectly to get them in the same order.

Now how to do i get the column "Acccount Token" to be a number format rather than scientific notation, which is what it is from the raw data,

and if there was a column with "Date" in it, how do i get it to MM/DD/YY format?

thanks in advance

[dhpeter83]

this doesn't necessarily have to be in the same macro, but rather a different macro to call, to encompass all the pages i may have?

Find
"Token" in column, apply number format

Find Date in columns, apply date format...make sense?

[jindon]

Need to see the data in a workbook.

[dhpeter83]

Okay, Let me remove the PCI data

[dhpeter83]

i2 Notebook no PCI.xlsm

See if this attachment works.

[jindon]

Find
"Token" in column, apply number format

Find Date in columns, apply date format...make sense?

I don't find any date column.

Is this a general question or related to your original question?
If not related, you will need to open a new thread since forum rule allows only one question per thread.

[dhpeter83]

I don't find any date column.

Is this a general question or related to your original question?
If not related, you will need to open a new thread since forum rule allows only one question per thread.

There is a "Trans Date" Column where the data is further down, and in a 24 hour time format.

It's relevant to this question because it's the same project, though a different task i guess. I can open a separate thread.

I'll call it

Macro to find columns and format