I have wrote a macro which allows the user to select a chart in a workbook
either on its own sheet or as a chart obj within a sheet and then run the
macro. When the macro is run it finds and highlights (Bright Green) all of
the data columns that are directly linked to the chart series no matter how
many sheets or other charts there are in the workbook. Then if any of the
primary data columns that are being charted have precedent columns of data
(ie. linked formulas) It highlights all of the precedent columns of data in a
dark green. what you are left with is all data relvent ONLY to the chart you
selected before running the macro is highlighted green making it easy to find
the data in a sheet that might have data for up to 50 different charts.

So that being said it is the second part of my macro that is not working.
Once the data has been highlighted I want to have VBA select all columns
which are not green (dark or light) and than delete those columns so that all
your left with on the spread sheet is data which is directly or indirectly
being used to plot the chart you ran the macro on.

Now I tried using an Array to get all of the column letters in order to
string them together in a range and than have them all simultaniously
selected and than delete them.

example ...
Range("A:A,C:C,R:R,S:S,T:T").Select
Selection.Delete

However this will not work if the String in the Range command is too long
So I tried the reverse Selecting all the columns in the spread sheet and
than deselecting only the ones I didn't want deleted, however aparently you
cant do that either.

So Any suggestions would be Greatly appreciated.

Dan Thompson

One way would be something like this...

dim rngToDelete as Range

set rngToDelete = columns("A")
set rngToDelete = union (rngToDelete, columns("C"))
set rngToDelete = union (rngToDelete, columns("R"))
set rngToDelete = union (rngToDelete, columns("S"))
set rngToDelete = union (rngToDelete, columns("T"))
'....
rngToDelete.Delete

--
HTH...

Jim Thomlinson


"Dan Thompson" wrote:

I have wrote a macro which allows the user to select a chart in a workbook
either on its own sheet or as a chart obj within a sheet and then run the
macro. When the macro is run it finds and highlights (Bright Green) all of
the data columns that are directly linked to the chart series no matter how
many sheets or other charts there are in the workbook. Then if any of the
primary data columns that are being charted have precedent columns of data
(ie. linked formulas) It highlights all of the precedent columns of data in a
dark green. what you are left with is all data relvent ONLY to the chart you
selected before running the macro is highlighted green making it easy to find
the data in a sheet that might have data for up to 50 different charts.

So that being said it is the second part of my macro that is not working.
Once the data has been highlighted I want to have VBA select all columns
which are not green (dark or light) and than delete those columns so that all
your left with on the spread sheet is data which is directly or indirectly
being used to plot the chart you ran the macro on.

Now I tried using an Array to get all of the column letters in order to
string them together in a range and than have them all simultaniously
selected and than delete them.

example ...
Range("A:A,C:C,R:R,S:S,T:T").Select
Selection.Delete

However this will not work if the String in the Range command is too long
So I tried the reverse Selecting all the columns in the spread sheet and
than deselecting only the ones I didn't want deleted, however aparently you
cant do that either.

So Any suggestions would be Greatly appreciated.

Dan Thompson