Re-Order Cols and Delete Cols when a List of Col Names in Order is Given using VBA

**e4excel** · 12-24-2022, 04:03 PM

Please Login or Register  to view this content.

MERRY CHRISTMAS TO EVERYONE AND SEASONS GREETINGS...

Dear Forum,

I am already working on a code to Re-Arrange the Column Order from a RAW DATA extracted from a Business Intelligence BI Tool which has excess columns and not all columns are relevant to my work so I need to not only RE-ORDER or ARRANGE their ORDER but also DELETE the remaining COLUMNS which are irrelevant to me.

I have three Sheets in the Sample File, one is the RAW DATA which has 26 Columns - with the Column Headers such as COL A, COL B ,.... till COL Z in the Sheet - Original Column Order , then in the other Sheet - Ordering Sheet - I have maintained as List of all the Columns which I need to be Arranged in the Order as mentioned in the Column A, now this Range from ("A2:A10") is of the ORDER of the Columns as well as I need to only keep the Columns which have this name in this order and then delete the remaining Columns.

I found some code online which I managed to tweak but in vain not got the desired result till now, so some experts can really help me on this..
I need to keep this RANGE Dynamic Always as in Future I would like to actually build a Macro to actually capture and Transpose all the Column Headers from the Sheet - Original Column Header in the Ordering Sheet..

Please Login or Register  to view this content.

**jindon** · 12-24-2022, 11:42 PM

Simple AdvancedFilter

Please Login or Register  to view this content.

**e4excel** · 12-25-2022, 12:28 AM

Thanks Jindon, It works really well however can you please explain the CODE as I am no expert ...
Dim x - Is it a Variant ?

Very Nice Crisp Code but please explain...as I would also be creating the Ordering Sheet using a Macro from the Original Sheet like Copy all the Column Headers from the Original Column Order Sheet and then Transpose and then Maybe add a Yes / No or the Numbers 1,2,3 to decide the ORDER of the Columns in the adjacent Column to decide which Columns to Keep in the Final Order Col Sheet.

SO I was thinking of using an Array to store the Column Headers and then pass the same to be SORTED and REMOVED accordingly...

**jindon** · 12-25-2022, 12:36 AM

Originally Posted by e4excel

Dim x - Is it a Variant ?

Any variables declared without data type are Variant in VBA.

Originally Posted by e4excel

Very Nice Crisp Code but please explain...as I would also be creating the Ordering Sheet using a Macro from the Original Sheet like Copy all the Column Headers from the Original Column Order Sheet and then Transpose and then Maybe add a Yes / No or the Numbers 1,2,3 to decide the ORDER of the Columns in the adjacent Column to decide which Columns to Keep in the Final Order Col Sheet.

Explain what?

Originally Posted by e4excel

SO I was thinking of using an Array to store the Column Headers and then pass the same to be SORTED and REMOVED accordingly...

I think you are on the right track, so go for it.

**e4excel** · 12-25-2022, 10:23 AM

Originally Posted by jindon

Simple AdvancedFilter

Please Login or Register  to view this content.

THanks in advance

**e4excel** · 12-26-2022, 02:42 AM

Dear Forum,

With my Original Code, can someone please explain as to where was I going wrong ? I actually wanted to pass a Range to the Array so that it would always be more Dynamic , but somewhere Im missing something...
SO can someone also please help me also Modify My COde so that this becomes a good learning..

The Solution from Jindon is great but since I too had started I would also like to build the code already started so please can someone help me on this..

Please Login or Register  to view this content.

**e4excel** · 12-30-2022, 02:42 AM

Dear Forum,

Can someone please help me with my approach too..

**Marc L** · 12-30-2022, 10:01 AM

Hello,

as your approach is very not the way to go, useless & overcomplicated,
if I have answered before jindon I should post the same Excel basics advanced filter way …

**Sintek** · 12-30-2022, 10:49 AM

Just for the fun of it...Here is another Option...

Please Login or Register  to view this content.

Re-Order Cols and Delete Cols when a List of Col Names in Order is Given using VBA

LinkBack

Thread Tools

Rate This Thread

Display

Re-Order Cols and Delete Cols when a List of Col Names in Order is Given using VBA

Re: Re-Order Cols and Delete Cols when a List of Col Names in Order is Given using VBA

Re: Re-Order Cols and Delete Cols when a List of Col Names in Order is Given using VBA

Re: Re-Order Cols and Delete Cols when a List of Col Names in Order is Given using VBA

Re: Re-Order Cols and Delete Cols when a List of Col Names in Order is Given using VBA

Re: Re-Order Cols and Delete Cols when a List of Col Names in Order is Given using VBA

Re: Re-Order Cols and Delete Cols when a List of Col Names in Order is Given using VBA

Re: Re-Order Cols and Delete Cols when a List of Col Names in Order is Given using VBA

Re: Re-Order Cols and Delete Cols when a List of Col Names in Order is Given using VBA

Thread Information

Users Browsing this Thread

Similar Threads

RT Error 424-Obj Required, when UNIONing 2 ranges in order to sync 2 cols of filtered data

[SOLVED] Sum columns (duplicates in row header) & delete duplicate cols to leave single unique cols

conditional formatting two cols such that both cols get same color based on a sum

Range.Select 1st pass 13 cols, 2nd paqss 25 cols twice as wide in error?

Bookmarks

Bookmarks

Posting Permissions