Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    NonContiguous selection (2003)

    Greetings,

    I have a situation where I need to delete data from nonContiguous columns. I recorded the below short macro, but am unclear as how to expand to select the columns that are not Contiguous. I have 33 columns to deal with. The starting point will allways be the second row of the Column EX: O2 or AB2 etc...



    Range("O2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents


    Thanks for any assistance,
    Brad

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: NonContiguous selection (2003)

    What columns do you want to delete? You could do a simple loop combining all the regions with the UNION method and then clear them all at once.

    Steve

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NonContiguous selection (2003)

    Steve,
    I do not want to remove the column headers, or the actual columns as I have pivot tables, other macros dealing with specific columns. I simply want to remove the data in 33 columns starting in the second cell of each column.

    I am unfamilar with the UNION function.

    Brad

  4. #4
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: NonContiguous selection (2003)

    Try the following<div style="width: 100%; background-color: #FFFFFF;"><code><font color=black><font color=blue>Option</font color=blue> <font color=blue>Explicit</font color=blue>
    <font color=blue>Sub</font color=blue> Clear4Brad()
    <font color=blue>Dim</font color=blue> i <font color=blue>As</font color=blue> <font color=blue>Long</font color=blue>
    <font color=blue>Dim</font color=blue> LR <font color=blue>As</font color=blue> Long
    Dim Cols(1 <font color=blue>To</font color=blue> 33) <font color=blue>As</font color=blue> <font color=blue>String</font color=blue>
    <font color=448800>'Populate Cols(1) through Cols(33) with _
    the columns to be cleared</font color=448800>
    Cols(1) = "C"
    Cols(2) = "AB"
    <font color=blue>For</font color=blue> i = 1 <font color=blue>To</font color=blue> 33
    LR = Range(Cols(i) & "2").End(xlDown).Row
    Range(Cols(i) & "2", Cols(i) & LR).Clear
    <font color=blue>Next</font color=blue> i
    <font color=blue>End</font color=blue> <font color=blue>Sub</font color=blue>
    </font color=black></code></div hiblock>
    Regards
    Don

  5. #5
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NonContiguous selection (2003)

    Cool,


    Will give it a try.....and reply back.


    Brad

  6. #6
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NonContiguous selection (2003)

    Wow,

    talk about quick. I really like that one....

    Thanks Don....


    Brad

  7. #7
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: NonContiguous selection (2003)

    If there is a possibility of data residing below the first blank cell in the column of interest, you might be better using the following:
    <div style="width: 100%; background-color: #FFFFFF;"><code><font color=black><font color=blue>Option</font color=blue> <font color=blue>Explicit</font color=blue>
    <font color=blue>Sub</font color=blue> Clear4Brad()
    <font color=blue>Const</font color=blue> j = 33
    <font color=blue>Dim</font color=blue> i <font color=blue>As</font color=blue> <font color=blue>Long</font color=blue>
    <font color=blue>Dim</font color=blue> LR <font color=blue>As</font color=blue> Long
    Dim Cols(1 <font color=blue>To</font color=blue> j) <font color=blue>As</font color=blue> <font color=blue>String</font color=blue>
    <font color=448800>'Populate Cols(1) through Cols(33) with _
    the columns to be cleared</font color=448800>
    Cols(1) = "C"
    Cols(2) = "AB"
    <font color=blue>For</font color=blue> i = 1 <font color=blue>To</font color=blue> j
    <font color=blue>If</font color=blue> <font color=blue>Not</font color=blue> Range(Cols(i) & "2") = "" <font color=blue>Then</font color=blue>
    LR = Range(Cols(i) & "2").End(xlDown).Row
    Range(Cols(i) & "2", Cols(i) & LR).Clear
    <font color=blue>End</font color=blue> <font color=blue>If</font color=blue>
    <font color=blue>Next</font color=blue> i
    <font color=blue>End</font color=blue> <font color=blue>Sub</font color=blue></font color=black></code></div hiblock>
    Regards
    Don

  8. #8
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NonContiguous selection (2003)

    Don,
    Thanks, That is a definite posibility of have blank in the data.

    I have added the code,


    Thanks!

    Brad

  9. #9
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NonContiguous selection (2003)

    Don (or someone)...

    I changed my code to be like the latest that Don offers, however I am getting an error at the

    If Not Range(Cols(i) & "2") = "" Then

    Not sure how to handle this issue.


    Regards,
    Brad

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: NonContiguous selection (2003)

    Do you want:

    If Range(Cols(i) & "2") <> "" Then

    Steve

    PS: what is the value of Cols(i) at this point?

  11. #11
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NonContiguous selection (2003)

    Steve,
    Not sure "at what point" you mean.

    Basically, the code is to select the Columns indicated, and delete all the contents. Don augmented his original code to check for blanks, if blanks found, then it was to proceed down past the cell and continue purging the information.

    I think the '<>' should work. Will try and let you know.


    Thanks,
    Brad

  12. #12
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NonContiguous selection (2003)

    Steve,

    No go....

    With the code changed as Don suggests, and with the change you suggest I get no action to the columns. The data remains.


    Any thoughts? Here is what I currently have.

    Option Explicit
    Sub Clear4Brad()
    Const j = 33
    Dim i As Long
    Dim LR As Long
    Dim Cols(1 To 33) As String
    'Populate Cols(1) through Cols(33) with _
    the columns to be cleared
    Cols(1) = "O"
    Cols(2) = "Z"
    Cols(3) = "AE"
    Cols(4) = "AG"
    Cols(5) = "AK"
    Cols(6) = "AL"
    Cols(7) = "AM"
    Cols(8) = "AN"
    Cols(9) = "AP"
    Cols(10) = "AT"
    Cols(11) = "AX"
    Cols(12) = "AY"
    Cols(13) = "AZ"
    Cols(14) = "BA"
    Cols(15) = "BB"
    Cols(16) = "BC"
    Cols(17) = "BH"
    Cols(18) = "BI"
    Cols(19) = "BL"
    Cols(20) = "BU"
    Cols(21) = "BY"
    Cols(22) = "CC"
    Cols(23) = "CJ"
    Cols(24) = "CL"
    Cols(25) = "CM"
    Cols(26) = "DL"
    Cols(27) = "ES"
    Cols(28) = "FL"
    Cols(29) = "FU"
    Cols(30) = "FW"
    Cols(31) = "FX"
    Cols(32) = "GB"
    Cols(33) = "GQ"

    For i = 1 To j
    If Not Range(Cols(i) & "2") <> "" Then
    LR = Range(Cols(i) & "2").End(xlDown).Row
    Range(Cols(i) & "2", Cols(i) & LR).Clear
    End If
    Next i
    End Sub


    ' LR = Range(Cols(i) & "2").End(xlDown).Row
    ' Range(Cols(i) & "2", Cols(i) & LR).Clear
    ' Next i
    ' End Sub


    Brad

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: NonContiguous selection (2003)

    You should either use
    <code>
    If Not Range(Cols(i) & "2") = "" Then
    </code>
    or
    <code>
    If Range(Cols(i) & "2") <> "" Then
    </code>
    Your line
    <code>
    If Not Range(Cols(i) & "2") <> "" Then
    </code>
    is equivalent to
    <code>
    If Range(Cols(i) & "2") = "" Then
    </code>
    which was not what you wanted, I think.

  14. #14
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NonContiguous selection (2003)

    Hans,
    I tried both of your suggestions, and it still stops emptying the cells if it finds an already empty cell.

    Example:

    Column 'O'

    Starting in Cell O2,
    Row Column

    r2 FALSE
    r3 FALSE
    r4 FALSE
    r5 FALSE
    r6 FALSE
    r7 FALSE

    etc....

    If all the cells are full with data, it will empty them. If there is a blank between the data, it stops emptying the cells.

    Thanks,
    Brad

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: NonContiguous selection (2003)

    Yes, that is by intention. Don Wells assumed that you did *not* want to delete cells below the first blank and you confirmed that (I think). If you want to clear all cells except the column header, change the line

    LR = Range(Cols(i) & "2").End(xlDown).Row

    to

    LR = Rows.Count

Page 1 of 2 12 LastLast

Posting Permissions

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