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

[Chirag R Raval]

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,

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

 

[Chirag R Raval]

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

 

[Marc L]

Hi !

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

 

[Chihiro]

Just use Union on ranges.

 

[Chirag R Raval]

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.



Hope there are some solution for this thread here.

Regards,

Chirag Raval

 

[Marc L]

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]

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

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.





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

Hope I described requirement clearly.

Regards,

Chirag Raval

 

[Marc L]

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
​

 

[Chirag R Raval]

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

 

[Marc L]

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 !

But who want to work on column numbers

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]

Dear Sir @Marc L ,

Thanks for your guideline on which I need to study that where I make mistake?

Till Then I found below link on web that very important to any one who search about & should must understand about application.convertformula method.

https://www.engram9.info/excel-vba-macros/members-that-produce-actions.html

Regards,

Chirag Raval

 

[Chirag R Raval]

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



Current Region



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

 

[Chihiro]

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.

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:

Union(Range("A1:A100"), Range("D1:D100"),...).Font.Color = vbRed

etc, etc.

Edit: Added double quotes around Column Letter for clarity (to signify string).

 

[Marc L]

why application.converfirmula not work on range variable? but work only on entire sheet?

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 …

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

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


At least crystal clear explain your need with an attachment according to it.
​

 

[Marc L]

My try to use usedrange & currentregion as below

Very bad use ‼ But I already gave samples in post #8 !
​

 

[Marc L]

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

 

[Chihiro]

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]

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

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

 

[Marc L]

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]

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