1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Magical 1 (One) liner code, for work on multiple columns ,contiguous ,with noncontiguous

Discussion in 'VBA Macros' started by Chirag R Raval, Apr 18, 2018.

  1. Chirag R Raval

    Chirag R Raval Member

    Messages:
    631
    Dear all users,

    I found through web, Magical 1 liner code , for work on multiple contiguous or Non contiguous columns. (multiple single columns or multiple range of columns, you can use both different aspects, through cover both in one line , in one shot)

    Though , it not seems 1 liner at any angle, but,
    after study ,practice & understand, Please remove not required part from code so its really transform in little.

    Hope users who in search for code to process on multiple columns ,
    below code will be very helpful them,

    Code (vb):

    Option Explicit

    'AMAZING ONE LINER CODE FOR DELETE /SELECT/PROCESS MULTIPLE COLUMNS( CONTIGUOUS OR NON CONTIGUOUS) IN ONE SHOT
    'BELOW SIMPLE EXAMPLE IS FOR PROCESS ON COLUMNS , AS DECLARE IT AS A RANGE OBJECT

    Sub DelSelProcesMultiColumns()

    Dim MyRng As Range 'If your approach to access this columns as range object

    Dim COLTXT As String 'for give reference of column names  as string

    'Most Important & useful Note:-
    'Range syntax is also acceptable: "C4:C5, C7:C8, C9,C11"

    COLTXT = "C3:C8,C10,C12,C14:C19"

    '        ActiveSheet.Range(Application.ConvertFormula(COLTXT, xlR1C1, xlA1)).Select
                                     
                                                  'OR

    ' ActiveSheet.Range(Application.ConvertFormula(COLTXT, xlR1C1, xlA1)).delete

    'You can direct access this range  through just set this as a range object

    'CORE POINT OF THIS CODE IS BELOW

      Set MyRng = ActiveSheet.Range(Application.ConvertFormula(COLTXT, xlR1C1, xlA1))
     
        With MyRng 'can access anything (Property Or Methods) after . (Dot)

    ' With Selection

            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False

    'If your already use this range as object, then  delete this variable as after work complete , as follows

    Set MyRng = Nothing

    End With

    End Sub
     
  2. Chirag R Raval

    Chirag R Raval Member

    Messages:
    631
    Dear Sirs,

    One drawback of above code is its process Entire Column, I want to process on multiple non-contiguous columns of particular range, not entire column.

    Anyone have any idea that how to apply above code on particular named range OR stetted range OR with End with statement?

    Hope there are some solution will find here.

    Regards,

    Chirag Raval
  3. Marc L

    Marc L Excel Ninja

    Messages:
    4,253
    Hi !

    Just clearly explain your need with an attachment
    as it often depends of the worksheet layout …
  4. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,940
    Just use Union on ranges.
  5. Chirag R Raval

    Chirag R Raval Member

    Messages:
    631
    Dear Sir @Marc L & Sir @Chihiro ,

    I don't want to process entire column of sheet, I want just process on till range's column's end. so can avoid blank formatting for counting used range.

    If there are multiple ranges below this range , all are effected by this code,
    that should be not require.

    This unnecessary formatting below actual used range can effect file size and need to further reset actual used range & may be divert wrong range.

    below is screen shot of requirement & also upload file with code & data for test.

    REQUIREMENT.jpg

    Hope there are some solution for this thread here.

    Regards,

    Chirag Raval

    Attached Files:

  6. Marc L

    Marc L Excel Ninja

    Messages:
    4,253

    I do not see any issue with your attachment
    maybe as it not matches with your screenshot !
    As usual UsedRange works and CurrentRegion as well …
    Chirag R Raval likes this.
  7. Chirag R Raval

    Chirag R Raval Member

    Messages:
    631
    Dear Sir @Marc L,

    Clearly shown in attached image that code select columns beyond end of range At row no 2237 & its select entire column.

    I just try as per below as per guideline to define dynamic range

    Code (vb):

    Dim myrng As Range 'If your approach to access this columns as range object
    Dim lastrow As Long

    lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    Set myrng = ActiveSheet.Range("A1:W" & ActiveSheet.Range("A1").End(xlDown).Row)

    Dim COLTXT As String 'for give reference of column names  as string

    'Note:- Range syntax is also acceptable: "C4:C5, C7:C8, C9,C11"

    COLTXT = "C3:C8,C10,C12,C14:C19"


      ActiveSheet.myrng(Application.ConvertFormula(COLTXT, xlR1C1, xlA1)).Select


        With myrng 'can access anything (Property Or Methods) after . (Dot)

     
    But its till select entire column not columns till end of range of range OR
    displayed below error.

    invalid procedhure.jpg



    I want to process/select/access columns of range that end on 2237 (dynamic)

    Hope I described requirement clearly.

    Regards,

    Chirag Raval
  8. Marc L

    Marc L Excel Ninja

    Messages:
    4,253
    I don't think the error comes from « entire column »
    when watching your error screenshot,
    more guessing a wrong use of ConvertFormula, why need it ?…
    Check the range address from the code.
    No issue with your small attachment via usual ways
    whatever using CurrentRegion, End(), Find or UsedRange

    Debug.Print ActiveSheet.UsedRange.Columns("A:W").Address

    Debug.Print [A1].CurrentRegion.Columns("A:W").Address
  9. Chirag R Raval

    Chirag R Raval Member

    Messages:
    631
    Dear Sir @Marc L,

    Yes, I just want to pointing towards it.
    Only ward "Range" is work there Before "(Application.convertformula)
    Not work "myrng " or anything else range variable
    Even not "With myrng",Even I try to cover whole
    Construct as object like " set myrng =range(Application...).

    The point is how this one liner code can work on only
    Settled range and not outside range?.

    This one liner code is really very important mainly
    As it work on column number & main power of it that
    It accepts single, contiguous and noncontiguous columns
    This power can tremendous help to process on columns
    In one shot .

    I (And ever daily users) already in realised that in any excel database,
    properly arranged,defined & precisly formatted columns are key factor.

    And when any one who maintain with struggle columns arrangement,
    As which column should be on which order or which column should be after
    Or before which as per his or her logical order or any required logical order/
    indexed must need this code to work in one shot who want to work on column numbers.

    I also know that it is very very simple & easy to do same job with
    Column name like
    "Myrng.columns("A;A", "C:C", "F:J").anything you want to do".

    And this approach natively supported by excel itself.

    But who want to work on column numbers who have
    Requirement as number type then this code can work
    Like a charm in one shot.

    So how to use this code only columns of range and should not
    Entire column of sheet?

    Hope there are some solution will be there.

    Regards,

    Chirag Raval
    Last edited: May 6, 2018
  10. Marc L

    Marc L Excel Ninja

    Messages:
    4,253
    Yes, combining with a range variable is an error
    as you can see in ConvertFormula VBA inner help …

    And within this function you made another error
    on the address style reference !

    So first check what returns ConvertFormula
    as your code returns letters only and not numbers ‼


    But as this function is useless for your need
    and I never had to use it for selecting any range
    (as very often a good code not needs any Select) …
    Chirag R Raval likes this.
  11. Chirag R Raval

    Chirag R Raval Member

    Messages:
    631
  12. Chirag R Raval

    Chirag R Raval Member

    Messages:
    631
    Dear Sir @Marc L,

    Thanks for pointing.

    I know that should not select range.
    but I actually just test to access this range by select only just for test purpose.

    But there are many way will be open if we can process those columns when we can access it.

    We are actually want to process on column numbers to process,

    I have doubts, is this one liner code combine columns letters with our given column numbers to access exact column?

    code really work on columns that's true..

    so , how can we cover/ inherit our range variable in this construct?
    why application.converfirmula not work on range variable? but work only on entire sheet?


    My try to use usedrange & currentregion as below

    usedrange.jpg

    Current Region

    currentregion.jpg

    Can below can useful to select only range's columns and not entire columns of sheet?

    http://www.freevbcode.com/ShowCode.asp?ID=4303

    Regards,

    Chirag Raval
    Last edited: May 8, 2018
  13. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,940
    Not really. Why try to reinvent wheels?

    As well, you never did clearly explain how columns are chosen.

    At any rate, there is never a need to convert Column Letter to number, or vice versa. You can just use .Cells(Row#, "Column Letter") or .Cells(Row #, Column #) it will mean the same thing.

    If you really must convert column letter to number. Just use... unlike the function using CHR Code for conversion, it will work on Column AA and beyond.
    Code (vb):
    Sheet1.Cells(Row #, "Column Letter").Column
    So, if you want to format specific columns... you can do it in many fashion. The ones you are trying to use are just not suitable.

    1. Loop via column index/object and when column#/letter condition is met, format the column using last used row (variable).

    2. Construct UNION of range and format at once. This is typically faster than 1st method. You can either hard code, or loop to construct union range.
    Ex:
    Code (vb):
    Union(Range("A1:A100"), Range("D1:D100"),...).Font.Color = vbRed
    etc, etc.

    Edit: Added double quotes around Column Letter for clarity (to signify string).
    Last edited: May 8, 2018
    Chirag R Raval likes this.
  14. Marc L

    Marc L Excel Ninja

    Messages:
    4,253
    Your « entire column » result is just because you misuse this function,
    just check what it returns within your code.
    Maybe the reason why you think about "magic" as you misunderstood it …

    Yes but can be avoided just with Excel/VBA basics …


    At least crystal clear explain your need with an attachment according to it.
    Chirag R Raval likes this.
  15. Marc L

    Marc L Excel Ninja

    Messages:
    4,253
    Very bad use ‼ But I already gave samples in post #8 !
  16. Marc L

    Marc L Excel Ninja

    Messages:
    4,253
    Your entire columns result comes from your source string
    as it just contains entire columns reference !

    So if you want partial columns you must add rows reference
    so this function is useless in this case, better use usual references …

    Nothing magic once you'll understand it, just a bit more complex as usual.
    Chirag R Raval likes this.
  17. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,940
    Oh, I forgot. In this instance, you don't need UNION, since you are not looping through areas within range, or doing different manipulation to each area.
    Range("String for each range separated by ,") will do just fine.
    Chirag R Raval likes this.
  18. Chirag R Raval

    Chirag R Raval Member

    Messages:
    631
    Dear Sirs @Marc L & @Chihiro ,

    Yes , there are something that is not really Magical.

    But, seems you both experts are very near that point that we can access range's columns (contiguous ,with noncontiguous) with this simple code.

    Okay

    Code (vb):

    Sub test()
    COLTXT = "C3:C8,C10,C12,C14:C19"
    Range(Application.ConvertFormula(COLTXT, xlR1C1, xlA1)).Font.bold=true
    End Sub
     
    please help to access just dynamic range's columns and not entire column of sheet.

    Can it possible or not through this code or not?

    Please , your last opinion depend on this thread.

    if not possible then I can leave this thread gracefully till this point.

    & sorry for take your valuable time and effort to just this little my magical misunderstandings.

    Regards,

    Chirag Raval
  19. Marc L

    Marc L Excel Ninja

    Messages:
    4,253
    As yet wrote so again but last :

    to access a dynamic range with ConvertFormula is possible only with
    a good source formula as your COLTXT is set for entire columns
    so you must correct its string ! See again its VBA help and sample …

    Or with you actual bad COLTXT you can combine the function result
    with Intersect method but it's like a DIY ! So I never go this way
    and it can not be a « one shot » as you stated …

    Easier and usually possible without this function even with true columns #
    - not like your alphanumeric COLTXT ! - as you can see within this forum
    and all over the Web !

    As Excel is usually in A1 mode and not in R1C1,
    the easy way is to work with columns names.
    For example you want to work with DD column, what is its number ?!

    The reason why for example an advanced filter directly works
    with headers names as it is far easier than with columns # …
    Chirag R Raval likes this.
  20. Chirag R Raval

    Chirag R Raval Member

    Messages:
    631
    Dear Sir @ Marc L & Sir @Chihiro ,

    Yes , you open my eyes as real "Guru" who always try to
    Pointing towards right direction to his lost student .

    Really I miss from true , natural & safe path & goes to wrong way & your all guru's constant try to divert
    Me on right path in the world of knowledge, your efforts are invaluable.

    I realise now what is right highway & sorry about my wrong belief and miss demand.

    Thank you very much Sirs.
    & I change my mind as to use easy ,safe , native & natural Excel's column name
    Method to access columns of range or entire columns.

    Regards,

    Chirag Raval

Share This Page