+ Reply to Thread
Results 1 to 9 of 9

NEED HELP with Range command

  1. #1
    Dan Thompson
    Guest

    NEED HELP with Range command

    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.



  2. #2
    Tom Ogilvy
    Guest

    RE: NEED HELP with Range command

    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.

    >


  3. #3
    Dan Thompson
    Guest

    RE: NEED HELP with Range command

    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.

    > >


  4. #4
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243
    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...

  5. #5
    Dan Thompson
    Guest

    Re: NEED HELP with Range command

    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
    >
    >


  6. #6
    Tom Ogilvy
    Guest

    Re: NEED HELP with Range command

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    >>
    >>




  7. #7
    Dan Thompson
    Guest

    Re: NEED HELP with Range command

    I'll tell you what Tom If you got time to look at this code great if not
    don't worry about it.

    Anyhow if you do try to look at this then just paste the following code into
    an empty module. In order for the code to work you will need at least 2 or
    more data columns which are being graphed on either a chart as an object in a
    spread sheet or a chart on it's own Sheet. (Best to use Line Chart for test
    perpouses)

    now before I past the code I have flaged where the problem is ocuuring in my
    code just do a search with-in the code for the text *Problem Start*

    Ok ready here's the code..

    Option Base 1
    '#############################################################################
    ' Chart Series Data Tracer [MACRO]
    '
    'Import this module into a workbook with charts you wish to run it on.
    'Import this module into "Personal Workbook" to have access to it from any
    Workbook.
    '
    'To run simply select a chart object, or chart Sheet and run the macro.
    'The macro when run, will highlight the columns of data being used to
    'draw the chart you selected.
    '#############################################################################
    Sub CSDT()
    Dim ChartIndex As Integer, NumOfSeries As Integer, x As Integer
    Dim SeriesArray() As Variant, ColArray() As Variant
    With ActiveChart
    On Error Resume Next
    ChartIndex = .Parent.Index 'For chart objects within a spreadsheet
    If Err.Number = 438 Then 'Error Ocures if the chart selected is not a
    chart object with in a spread sheet
    ChartIndex = .Index 'For a chart that is it's own sheet(chart sheet)
    End If
    NumOfSeries = .SeriesCollection.Count
    End With

    ReDim SeriesArray(NumOfSeries)
    ReDim ColArray(NumOfSeries)
    For x = 1 To NumOfSeries
    SeriesArray(x) = ActiveChart.SeriesCollection(x).Formula
    ColArray(x) = DataCol(SeriesArray(x))
    Next x
    SourceWrksheet = GetSheetName(SeriesArray(1))
    Worksheets(SourceWrksheet).Activate
    For x = 1 To NumOfSeries
    Range(ColArray(x) & ":" & ColArray(x)).Select 'Selects source data
    column of a chart series
    Selection.Interior.ColorIndex = 4 'Colors chart series data column
    bright green
    Range(ColArray(x) & ":" &
    ColArray(x)).Precedents.Columns.EntireColumn.Select 'The Precedents command
    Returns a Range object that represents all the precedents(links) of a cell
    If Not Err.Number = 1004 Then 'Error 1004 is "No Cells Were Found"
    Meaning there are no Precedents(Links) for the cells.
    Selection.Interior.ColorIndex = 35 'colors all
    precendent(Linked) cells light pastell green
    End If
    Next x
    Err.Clear
    Resume
    GetNonGreenColPos
    End Sub
    Sub GetNonGreenColPos()
    Dim N As Integer, A As Integer, FirstOcurance As Integer, SecondOcurance As
    Integer
    Dim CurCelAddress As String, NonGreenColArray() As String, MyRangeString As
    String
    With ActiveSheet
    A = 0
    For N = 1 To 256
    If Not Cells(1, N).Interior.ColorIndex = 4 Then
    If Not Cells(1, N).Interior.ColorIndex = 35 Then
    CurCelAddress = Cells(1, N).Address
    FirstOcurance = InStr(1, CurCelAddress, "$")
    SecondOcurance = InStr(FirstOcurance + 1, CurCelAddress, "$")
    NonGreenCol = Mid(CurCelAddress, FirstOcurance + 1,
    SecondOcurance - (FirstOcurance + 1))
    A = A + 1
    ReDim Preserve NonGreenColArray(A)
    NonGreenColArray(A) = NonGreenCol
    End If
    End If
    Next N
    '*Problem Start*
    MyRangeString = CreateRangeString(NonGreenColArray())
    'If I use the CreateRangeString With a Small String it works
    'If I use it with a large string than MyStringRange is "" Nothing
    'and yet the next line returns a len of 1419 for MyStringRange
    'Whats up with that ???

    MsgBox (Len(MyRangeString))
    Range(MyString).Select

    End With
    End Sub
    Function CreateRangeString(ByRef NGCA() As String) As String
    Dim R As Integer
    Dim TestString As String

    Dim FString As String, MyString As String
    For R = 1 To UBound(NGCA())
    If Not R = UBound(NGCA()) Then
    MyString = NGCA(R) & ":" & NGCA(R) & ","
    Else
    MyString = NGCA(R) & ":" & NGCA(R)
    End If
    FString = FString + MyString
    Next R
    CreateRangeString = FString

    End Function
    Function GetSheetName(ByVal ChartSeriesString As String) As String
    GetSheetName = Mid(ChartSeriesString, InStr(1, ChartSeriesString, "'") + 1,
    InStr(InStr(1, ChartSeriesString, "'") + 1, ChartSeriesString, "'") -
    (InStr(1, ChartSeriesString, "'") + 1))
    End Function
    Function DataCol(ByVal DataRange As String) As String
    '*****************************************************
    'Returns single Column letter from a "A1" Style Range
    '
    'Example:
    ' MyRangeString = "'Data Calc'!$A$4:$A$20"
    ' MyColLetter = DataCol(MyRangeString)
    '
    ' Returns String: A
    '*****************************************************
    Dim T As Integer, x As Integer
    x = Len(DataRange)
    For T = 1 To x
    If Left(Right(DataRange, T), 1) = "!" Then
    If Left(Right(DataRange, T - 3), 1) = "$" Then
    DataCol = Left(Right(DataRange, T - 2), 1)
    Exit For
    Else
    DataCol = Left(Right(DataRange, T - 2), 2)
    Exit For
    End If
    End If
    Next T
    End Function


    "Tom Ogilvy" wrote:

    > 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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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
    > >>
    > >>

    >
    >
    >


  8. #8
    Tom Ogilvy
    Guest

    Re: NEED HELP with Range command

    I am not sure what you want to do with it, but this will select the columns:
    Better not to work with strings for when the string will get very long.
    (With your original code, I couldn't reproduce the specific problem you
    describe, but the code couldn't select the range - string was 1495 in
    length - too long).

    Sub GetNonGreenColPos()
    Dim N As Integer
    Dim rng As Range
    With ActiveSheet
    For N = 1 To 256
    If Not Cells(1, N).Interior.ColorIndex = 4 Then
    If Not Cells(1, N).Interior.ColorIndex = 35 Then
    If rng Is Nothing Then
    Set rng = Cells(1, N)
    Else
    Set rng = Union(rng, Cells(1, N))
    End If
    End If
    End If
    Next N
    Debug.Print rng.Address
    rng.EntireColumn.Select
    End With
    End Sub

    --
    Regards,
    Tom Ogilvy

    "Dan Thompson" <[email protected]> wrote in message
    news:[email protected]...
    > I'll tell you what Tom If you got time to look at this code great if not
    > don't worry about it.
    >
    > Anyhow if you do try to look at this then just paste the following code
    > into
    > an empty module. In order for the code to work you will need at least 2 or
    > more data columns which are being graphed on either a chart as an object
    > in a
    > spread sheet or a chart on it's own Sheet. (Best to use Line Chart for
    > test
    > perpouses)
    >
    > now before I past the code I have flaged where the problem is ocuuring in
    > my
    > code just do a search with-in the code for the text *Problem Start*
    >
    > Ok ready here's the code..
    >
    > Option Base 1
    > '#############################################################################
    > ' Chart Series Data Tracer [MACRO]
    > '
    > 'Import this module into a workbook with charts you wish to run it on.
    > 'Import this module into "Personal Workbook" to have access to it from any
    > Workbook.
    > '
    > 'To run simply select a chart object, or chart Sheet and run the macro.
    > 'The macro when run, will highlight the columns of data being used to
    > 'draw the chart you selected.
    > '#############################################################################
    > Sub CSDT()
    > Dim ChartIndex As Integer, NumOfSeries As Integer, x As Integer
    > Dim SeriesArray() As Variant, ColArray() As Variant
    > With ActiveChart
    > On Error Resume Next
    > ChartIndex = .Parent.Index 'For chart objects within a spreadsheet
    > If Err.Number = 438 Then 'Error Ocures if the chart selected is not a
    > chart object with in a spread sheet
    > ChartIndex = .Index 'For a chart that is it's own sheet(chart
    > sheet)
    > End If
    > NumOfSeries = .SeriesCollection.Count
    > End With
    >
    > ReDim SeriesArray(NumOfSeries)
    > ReDim ColArray(NumOfSeries)
    > For x = 1 To NumOfSeries
    > SeriesArray(x) = ActiveChart.SeriesCollection(x).Formula
    > ColArray(x) = DataCol(SeriesArray(x))
    > Next x
    > SourceWrksheet = GetSheetName(SeriesArray(1))
    > Worksheets(SourceWrksheet).Activate
    > For x = 1 To NumOfSeries
    > Range(ColArray(x) & ":" & ColArray(x)).Select 'Selects source data
    > column of a chart series
    > Selection.Interior.ColorIndex = 4 'Colors chart series data column
    > bright green
    > Range(ColArray(x) & ":" &
    > ColArray(x)).Precedents.Columns.EntireColumn.Select 'The Precedents
    > command
    > Returns a Range object that represents all the precedents(links) of a cell
    > If Not Err.Number = 1004 Then 'Error 1004 is "No Cells Were Found"
    > Meaning there are no Precedents(Links) for the cells.
    > Selection.Interior.ColorIndex = 35 'colors all
    > precendent(Linked) cells light pastell green
    > End If
    > Next x
    > Err.Clear
    > Resume
    > GetNonGreenColPos
    > End Sub
    > Sub GetNonGreenColPos()
    > Dim N As Integer, A As Integer, FirstOcurance As Integer, SecondOcurance
    > As
    > Integer
    > Dim CurCelAddress As String, NonGreenColArray() As String, MyRangeString
    > As
    > String
    > With ActiveSheet
    > A = 0
    > For N = 1 To 256
    > If Not Cells(1, N).Interior.ColorIndex = 4 Then
    > If Not Cells(1, N).Interior.ColorIndex = 35 Then
    > CurCelAddress = Cells(1, N).Address
    > FirstOcurance = InStr(1, CurCelAddress, "$")
    > SecondOcurance = InStr(FirstOcurance + 1, CurCelAddress,
    > "$")
    > NonGreenCol = Mid(CurCelAddress, FirstOcurance + 1,
    > SecondOcurance - (FirstOcurance + 1))
    > A = A + 1
    > ReDim Preserve NonGreenColArray(A)
    > NonGreenColArray(A) = NonGreenCol
    > End If
    > End If
    > Next N
    > '*Problem Start*
    > MyRangeString = CreateRangeString(NonGreenColArray())
    > 'If I use the CreateRangeString With a Small String it works
    > 'If I use it with a large string than MyStringRange is "" Nothing
    > 'and yet the next line returns a len of 1419 for MyStringRange
    > 'Whats up with that ???
    >
    > MsgBox (Len(MyRangeString))
    > Range(MyString).Select
    >
    > End With
    > End Sub
    > Function CreateRangeString(ByRef NGCA() As String) As String
    > Dim R As Integer
    > Dim TestString As String
    >
    > Dim FString As String, MyString As String
    > For R = 1 To UBound(NGCA())
    > If Not R = UBound(NGCA()) Then
    > MyString = NGCA(R) & ":" & NGCA(R) & ","
    > Else
    > MyString = NGCA(R) & ":" & NGCA(R)
    > End If
    > FString = FString + MyString
    > Next R
    > CreateRangeString = FString
    >
    > End Function
    > Function GetSheetName(ByVal ChartSeriesString As String) As String
    > GetSheetName = Mid(ChartSeriesString, InStr(1, ChartSeriesString, "'") +
    > 1,
    > InStr(InStr(1, ChartSeriesString, "'") + 1, ChartSeriesString, "'") -
    > (InStr(1, ChartSeriesString, "'") + 1))
    > End Function
    > Function DataCol(ByVal DataRange As String) As String
    > '*****************************************************
    > 'Returns single Column letter from a "A1" Style Range
    > '
    > 'Example:
    > ' MyRangeString = "'Data Calc'!$A$4:$A$20"
    > ' MyColLetter = DataCol(MyRangeString)
    > '
    > ' Returns String: A
    > '*****************************************************
    > Dim T As Integer, x As Integer
    > x = Len(DataRange)
    > For T = 1 To x
    > If Left(Right(DataRange, T), 1) = "!" Then
    > If Left(Right(DataRange, T - 3), 1) = "$" Then
    > DataCol = Left(Right(DataRange, T - 2), 1)
    > Exit For
    > Else
    > DataCol = Left(Right(DataRange, T - 2), 2)
    > Exit For
    > End If
    > End If
    > Next T
    > End Function
    >
    >
    > "Tom Ogilvy" wrote:
    >
    >> 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" <[email protected]> wrote in message
    >> news:[email protected]...
    >> > 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
    >> >>
    >> >>

    >>
    >>
    >>




  9. #9
    Dan Thompson
    Guest

    Re: NEED HELP with Range command

    I haven't tried your code yet as I just read your latest reply but I will see
    if it helps either way
    thankyou for your help.
    To answer your question about what I want do do with this code.

    Basicly I have alot of excel workbooks each one of these workbooks has
    anyhwere from 10 to 100 charts
    contained in it. there is alot of data in these workbooks (but only on one
    WorkSheet). I am trying to write a macro that will allow me to simply click
    on what ever chart I want, run the macro and it will locate all the relevant
    data that chart is linked to (dependent Upon) and delete any other data in
    the same worksheet and than give me the option to save the workbook under a
    different name after this has been done.

    Now everything in my code (at least for me) was working fine untill I ran
    into this string to long issue for the Range command.


    "Tom Ogilvy" wrote:

    > I am not sure what you want to do with it, but this will select the columns:
    > Better not to work with strings for when the string will get very long.
    > (With your original code, I couldn't reproduce the specific problem you
    > describe, but the code couldn't select the range - string was 1495 in
    > length - too long).
    >
    > Sub GetNonGreenColPos()
    > Dim N As Integer
    > Dim rng As Range
    > With ActiveSheet
    > For N = 1 To 256
    > If Not Cells(1, N).Interior.ColorIndex = 4 Then
    > If Not Cells(1, N).Interior.ColorIndex = 35 Then
    > If rng Is Nothing Then
    > Set rng = Cells(1, N)
    > Else
    > Set rng = Union(rng, Cells(1, N))
    > End If
    > End If
    > End If
    > Next N
    > Debug.Print rng.Address
    > rng.EntireColumn.Select
    > End With
    > End Sub
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    > "Dan Thompson" <[email protected]> wrote in message
    > news:[email protected]...
    > > I'll tell you what Tom If you got time to look at this code great if not
    > > don't worry about it.
    > >
    > > Anyhow if you do try to look at this then just paste the following code
    > > into
    > > an empty module. In order for the code to work you will need at least 2 or
    > > more data columns which are being graphed on either a chart as an object
    > > in a
    > > spread sheet or a chart on it's own Sheet. (Best to use Line Chart for
    > > test
    > > perpouses)
    > >
    > > now before I past the code I have flaged where the problem is ocuuring in
    > > my
    > > code just do a search with-in the code for the text *Problem Start*
    > >
    > > Ok ready here's the code..
    > >
    > > Option Base 1
    > > '#############################################################################
    > > ' Chart Series Data Tracer [MACRO]
    > > '
    > > 'Import this module into a workbook with charts you wish to run it on.
    > > 'Import this module into "Personal Workbook" to have access to it from any
    > > Workbook.
    > > '
    > > 'To run simply select a chart object, or chart Sheet and run the macro.
    > > 'The macro when run, will highlight the columns of data being used to
    > > 'draw the chart you selected.
    > > '#############################################################################
    > > Sub CSDT()
    > > Dim ChartIndex As Integer, NumOfSeries As Integer, x As Integer
    > > Dim SeriesArray() As Variant, ColArray() As Variant
    > > With ActiveChart
    > > On Error Resume Next
    > > ChartIndex = .Parent.Index 'For chart objects within a spreadsheet
    > > If Err.Number = 438 Then 'Error Ocures if the chart selected is not a
    > > chart object with in a spread sheet
    > > ChartIndex = .Index 'For a chart that is it's own sheet(chart
    > > sheet)
    > > End If
    > > NumOfSeries = .SeriesCollection.Count
    > > End With
    > >
    > > ReDim SeriesArray(NumOfSeries)
    > > ReDim ColArray(NumOfSeries)
    > > For x = 1 To NumOfSeries
    > > SeriesArray(x) = ActiveChart.SeriesCollection(x).Formula
    > > ColArray(x) = DataCol(SeriesArray(x))
    > > Next x
    > > SourceWrksheet = GetSheetName(SeriesArray(1))
    > > Worksheets(SourceWrksheet).Activate
    > > For x = 1 To NumOfSeries
    > > Range(ColArray(x) & ":" & ColArray(x)).Select 'Selects source data
    > > column of a chart series
    > > Selection.Interior.ColorIndex = 4 'Colors chart series data column
    > > bright green
    > > Range(ColArray(x) & ":" &
    > > ColArray(x)).Precedents.Columns.EntireColumn.Select 'The Precedents
    > > command
    > > Returns a Range object that represents all the precedents(links) of a cell
    > > If Not Err.Number = 1004 Then 'Error 1004 is "No Cells Were Found"
    > > Meaning there are no Precedents(Links) for the cells.
    > > Selection.Interior.ColorIndex = 35 'colors all
    > > precendent(Linked) cells light pastell green
    > > End If
    > > Next x
    > > Err.Clear
    > > Resume
    > > GetNonGreenColPos
    > > End Sub
    > > Sub GetNonGreenColPos()
    > > Dim N As Integer, A As Integer, FirstOcurance As Integer, SecondOcurance
    > > As
    > > Integer
    > > Dim CurCelAddress As String, NonGreenColArray() As String, MyRangeString
    > > As
    > > String
    > > With ActiveSheet
    > > A = 0
    > > For N = 1 To 256
    > > If Not Cells(1, N).Interior.ColorIndex = 4 Then
    > > If Not Cells(1, N).Interior.ColorIndex = 35 Then
    > > CurCelAddress = Cells(1, N).Address
    > > FirstOcurance = InStr(1, CurCelAddress, "$")
    > > SecondOcurance = InStr(FirstOcurance + 1, CurCelAddress,
    > > "$")
    > > NonGreenCol = Mid(CurCelAddress, FirstOcurance + 1,
    > > SecondOcurance - (FirstOcurance + 1))
    > > A = A + 1
    > > ReDim Preserve NonGreenColArray(A)
    > > NonGreenColArray(A) = NonGreenCol
    > > End If
    > > End If
    > > Next N
    > > '*Problem Start*
    > > MyRangeString = CreateRangeString(NonGreenColArray())
    > > 'If I use the CreateRangeString With a Small String it works
    > > 'If I use it with a large string than MyStringRange is "" Nothing
    > > 'and yet the next line returns a len of 1419 for MyStringRange
    > > 'Whats up with that ???
    > >
    > > MsgBox (Len(MyRangeString))
    > > Range(MyString).Select
    > >
    > > End With
    > > End Sub
    > > Function CreateRangeString(ByRef NGCA() As String) As String
    > > Dim R As Integer
    > > Dim TestString As String
    > >
    > > Dim FString As String, MyString As String
    > > For R = 1 To UBound(NGCA())
    > > If Not R = UBound(NGCA()) Then
    > > MyString = NGCA(R) & ":" & NGCA(R) & ","
    > > Else
    > > MyString = NGCA(R) & ":" & NGCA(R)
    > > End If
    > > FString = FString + MyString
    > > Next R
    > > CreateRangeString = FString
    > >
    > > End Function
    > > Function GetSheetName(ByVal ChartSeriesString As String) As String
    > > GetSheetName = Mid(ChartSeriesString, InStr(1, ChartSeriesString, "'") +
    > > 1,
    > > InStr(InStr(1, ChartSeriesString, "'") + 1, ChartSeriesString, "'") -
    > > (InStr(1, ChartSeriesString, "'") + 1))
    > > End Function
    > > Function DataCol(ByVal DataRange As String) As String
    > > '*****************************************************
    > > 'Returns single Column letter from a "A1" Style Range
    > > '
    > > 'Example:
    > > ' MyRangeString = "'Data Calc'!$A$4:$A$20"
    > > ' MyColLetter = DataCol(MyRangeString)
    > > '
    > > ' Returns String: A
    > > '*****************************************************
    > > Dim T As Integer, x As Integer
    > > x = Len(DataRange)
    > > For T = 1 To x
    > > If Left(Right(DataRange, T), 1) = "!" Then
    > > If Left(Right(DataRange, T - 3), 1) = "$" Then
    > > DataCol = Left(Right(DataRange, T - 2), 1)
    > > Exit For
    > > Else
    > > DataCol = Left(Right(DataRange, T - 2), 2)
    > > Exit For
    > > End If
    > > End If
    > > Next T
    > > End Function
    > >
    > >
    > > "Tom Ogilvy" wrote:
    > >
    > >> 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" <[email protected]> wrote in message
    > >> news:[email protected]...
    > >> > 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
    > >> >>
    > >> >>
    > >>
    > >>
    > >>

    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1