Selective columns delete

SMI// · January 5th, 2016, 02:23 AM

I have an Oracle based report which has about 223 columns across the sheet. I just want to retain about 12 columns out of this report but doing this every time manually is a cumbersome activity.

The easiest way I could think of was to use activesheet.range($A$2,$B$2,$C$2,$D$2,$E$2,$F$2... .).entirecolumn.delete

However, since the range is quite big, I get a compile error every time I try to run this.

Any suggestions?

Regards
SMI

Tom Urtis · January 6th, 2016, 05:58 PM

Which 12 of the columns do you want to keep?

Zakalwe · January 8th, 2016, 11:01 PM

Hi,

Why export what you do not need, from Oracle,in the 1st place?

Have you tried Using a union function on them then Iterate over the new range?

Code:

CombinedRange2Delete = Union(UnwantedRange1,UnrantedRange2, ... ,UnwantedRange223)

For each(Current in CombinedRange2Delete)
  Current.Delete
Next.

Another approach: Since there is more Unwanted collumns then it is maybe better to 1st isolate the Wanted then Delete any, from total columns, that do not intersect with the wanted columns. Get the total columns then the columns you want then delete any Total columns not intersecting with the wanted columns.

Code:

WantedColumns = Union(WantedRange1, WantedRange2, ..., WantedRange10)
TotalColumns = Range(Column1Name:Column223Name)

For each(CurrentColumn in TotalColumns)
  IntersectColumns = Intersect(CurrentColumn , WantedColumns)
  if IntersectColumns is Nothing then
        CurrentColumn.Delete
  end if
Next.

If you wanna chat about it: [email protected]