Thanks for the help Jim, however allthough the "visible=false" was a good Idea
unfortunitly it will not work for what I need to do.

You see basicly what I am doing is I have a working code that takes chart
series values and locates them in there corisponding spread sheet. The
problem is that I want to delete all of irrelavent data columns in the same
spread sheet, thus only leaving the cloumns that are linked to data series on
my chart.
however when I use the Range("A:A,B:B,F:F.... ect ..ect ) command it doesn't
work once I get too man columns listed I think I can get up to "AF" and than
the range command seems to run out of gas and goes dumb (Excel VBA just
ignors that line all together).

I thought of trying to do this with using the column command but havent
been able to get that to work etiher.

Any more thoughts Anyone ?


"Jim Jackson" wrote:

If you are copying certain columns to paste in another workbook you might
consider hiding the unwanted columns.

Columns("D:E").visible = false
Columns("G:AA").visible=false

etc.

then you can have Range("A1:DF3000").select
Selection.copy

The only columns copied will be the visible ones.

Jim


"Dan Thompson" wrote:

Hi All,
Ok I am using the Range command to select multiple columns like this..

Range("A:A,B:B,C:C,F:F,AB:AB,AC:AC,BF:BF").Select

The problem I am having is my actual range of columns includes many more
columns that the example above.

Is there a maximum to how man columns you can include to select with the
range command ?
because the range command fails when I get to man coluns listed like say 200
or more.
Is there another way to accomplish this ? because all I realy want to do is
have vba do the same thing as if I manual selected up to 200 columns with the
mouse while holding down the Ctrl button.

break it in two pieces.

to the ones farthest to the right first, then the ones to the left

--
Regards,
Tom Ogilvy


"Dan Thompson" wrote:

Thanks for the help Jim, however allthough the "visible=false" was a good Idea
unfortunitly it will not work for what I need to do.

You see basicly what I am doing is I have a working code that takes chart
series values and locates them in there corisponding spread sheet. The
problem is that I want to delete all of irrelavent data columns in the same
spread sheet, thus only leaving the cloumns that are linked to data series on
my chart.
however when I use the Range("A:A,B:B,F:F.... ect ..ect ) command it doesn't
work once I get too man columns listed I think I can get up to "AF" and than
the range command seems to run out of gas and goes dumb (Excel VBA just
ignors that line all together).

I thought of trying to do this with using the column command but havent
been able to get that to work etiher.

Any more thoughts Anyone ?


"Jim Jackson" wrote:

If you are copying certain columns to paste in another workbook you might
consider hiding the unwanted columns.

Columns("D:E").visible = false
Columns("G:AA").visible=false

etc.

then you can have Range("A1:DF3000").select
Selection.copy

The only columns copied will be the visible ones.

Jim


"Dan Thompson" wrote:

Hi All,
Ok I am using the Range command to select multiple columns like this..

Range("A:A,B:B,C:C,F:F,AB:AB,AC:AC,BF:BF").Select

The problem I am having is my actual range of columns includes many more
columns that the example above.

Is there a maximum to how man columns you can include to select with the
range command ?
because the range command fails when I get to man coluns listed like say 200
or more.
Is there another way to accomplish this ? because all I realy want to do is
have vba do the same thing as if I manual selected up to 200 columns with the
mouse while holding down the Ctrl button.

Yes I thought of that but I didn't figure that it would work because when I
delete the first half the columns will shift but I can't belive I didn't
think of starting from the left.

Thanks for the suggestion Tom.

Only thing is it might still not work very well cause I might have to break
it in like 6 or more peices but I'll give it a try.

Hey btw Tom you realize your like famous on this newsgroup now.
I allways hope that I will get a reply from you when I post :)

You have helped me out several times over the last few years.

Dan Thompson aka DTM (Dan The Man)


"Tom Ogilvy" wrote:

break it in two pieces.

to the ones farthest to the right first, then the ones to the left

--
Regards,
Tom Ogilvy


"Dan Thompson" wrote:

Thanks for the help Jim, however allthough the "visible=false" was a good Idea
unfortunitly it will not work for what I need to do.

You see basicly what I am doing is I have a working code that takes chart
series values and locates them in there corisponding spread sheet. The
problem is that I want to delete all of irrelavent data columns in the same
spread sheet, thus only leaving the cloumns that are linked to data series on
my chart.
however when I use the Range("A:A,B:B,F:F.... ect ..ect ) command it doesn't
work once I get too man columns listed I think I can get up to "AF" and than
the range command seems to run out of gas and goes dumb (Excel VBA just
ignors that line all together).

I thought of trying to do this with using the column command but havent
been able to get that to work etiher.

Any more thoughts Anyone ?


"Jim Jackson" wrote:

If you are copying certain columns to paste in another workbook you might
consider hiding the unwanted columns.

Columns("D:E").visible = false
Columns("G:AA").visible=false

etc.

then you can have Range("A1:DF3000").select
Selection.copy

The only columns copied will be the visible ones.

Jim


"Dan Thompson" wrote:

Hi All,
Ok I am using the Range command to select multiple columns like this..

Range("A:A,B:B,C:C,F:F,AB:AB,AC:AC,BF:BF").Select

The problem I am having is my actual range of columns includes many more
columns that the example above.

Is there a maximum to how man columns you can include to select with the
range command ?
because the range command fails when I get to man coluns listed like say 200
or more.
Is there another way to accomplish this ? because all I realy want to do is
have vba do the same thing as if I manual selected up to 200 columns with the
mouse while holding down the Ctrl button.

Hi Dan,
Tom's given you the best answer already (split into 2 lots & start from
the right) but just to expand on it...

Your initial post stated:
Range("A:A,B:B,C:C,F:F,AB:AB,AC:AC,BF:BF").Select

This can be shortened to include any consecutive columns in the same
range ie
Range("A:C,F:F,AB:AC,BF:BF").Select
(This would happen if you left click on the first column eg A & then
held it down while dragging the mouse over the columns next to it eg B
& C.)

If you are just deleting them (I think?) it can be safely changed to
Range("A:C,F:F,AB:AC,BF:BF").delete

hth
Rob Brockett
NZ
always learning & the best way to learn is to experience...


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=560324

Yes thank you bro, I apreciate the concept of using the A:F,H:J ect.....
however the rest of the code is based on dynamic information so I have know
way of knowing if it is going to be A:H or C:H or A:D you get the Idea.
The only way I can posibly think of that would work is to write some kind of
code that would be smart enought to look at a line like this

MyString =
"A:A,B:B,C:C,D:D,E:E,F:F,G:G,H:H,K:K,N:N,Q:Q,R:R,S :S,T:T,X:X,AA:AA" ect....

and be able to have some code convert the above line into somthing like the
following

MyString = "A:H,K:K,N:N,Q:T,X:X,AA:AA"

But I didn't realy want to have to write code to do that because it seams
like alot of extra work when VBA should just be able to handle really long
strings like the first one assigned to a Range

example: MyRange = Range(MyString).Select

keep in mind this will work with the example I had above because it is short
enough, I just didn't want to type in the actual string from my code because
it is like 220 characters long or more.


"broro183" wrote:


Hi Dan,
Tom's given you the best answer already (split into 2 lots & start from
the right) but just to expand on it...

Your initial post stated:
Range("A:A,B:B,C:C,F:F,AB:AB,AC:AC,BF:BF").Select

This can be shortened to include any consecutive columns in the same
range ie
Range("A:C,F:F,AB:AC,BF:BF").Select
(This would happen if you left click on the first column eg A & then
held it down while dragging the mouse over the columns next to it eg B
& C.)

If you are just deleting them (I think?) it can be safely changed to
Range("A:C,F:F,AB:AC,BF:BF").delete

hth
Rob Brockett
NZ
always learning & the best way to learn is to experience...


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=560324

Thanks for the kind words - hopefully I can live up to them.

Perhaps a different approach:
are you looping and using some criteria to see if the column should be
deleted? for demonstration, I will check if the cell in row 1 has a value
greater than 3:

Dim rng as Range, i as Long
for i = 100 to 1 step -1
if cells(1,i) 3 then
if rng is nothing then
set rng = cells(1,i)
else
set rng = union(cells(1,i),rng)
end if
end if
Next
if not rng is nothing then
'rng.EntireColumn.Delete
rng.EntireColumn.Select
end if


--
Regards,
Tom Ogilvy

"Dan Thompson" wrote in message
...
Yes thank you bro, I apreciate the concept of using the A:F,H:J ect.....
however the rest of the code is based on dynamic information so I have
know
way of knowing if it is going to be A:H or C:H or A:D you get the Idea.
The only way I can posibly think of that would work is to write some kind
of
code that would be smart enought to look at a line like this

MyString =
"A:A,B:B,C:C,D:D,E:E,F:F,G:G,H:H,K:K,N:N,Q:Q,R:R,S :S,T:T,X:X,AA:AA"
ect....

and be able to have some code convert the above line into somthing like
the
following

MyString = "A:H,K:K,N:N,Q:T,X:X,AA:AA"

But I didn't realy want to have to write code to do that because it seams
like alot of extra work when VBA should just be able to handle really long
strings like the first one assigned to a Range

example: MyRange = Range(MyString).Select

keep in mind this will work with the example I had above because it is
short
enough, I just didn't want to type in the actual string from my code
because
it is like 220 characters long or more.


"broro183" wrote:


Hi Dan,
Tom's given you the best answer already (split into 2 lots & start from
the right) but just to expand on it...

Your initial post stated:
Range("A:A,B:B,C:C,F:F,AB:AB,AC:AC,BF:BF").Select

This can be shortened to include any consecutive columns in the same
range ie
Range("A:C,F:F,AB:AC,BF:BF").Select
(This would happen if you left click on the first column eg A & then
held it down while dragging the mouse over the columns next to it eg B
& C.)

If you are just deleting them (I think?) it can be safely changed to
Range("A:C,F:F,AB:AC,BF:BF").delete

hth
Rob Brockett
NZ
always learning & the best way to learn is to experience...


--
broro183
------------------------------------------------------------------------
broro183's Profile:
http://www.excelforum.com/member.php...o&userid=30068
View this thread:
http://www.excelforum.com/showthread...hreadid=560324