Convert Numbers to Letters 3

[Sylv4n]

What I want to do is:
I have Row 1 with information in it from Column B onward, I dont know where it finishes as it will vary.

What I want to do is Select From Colum B to the last entry.

I have found one way of doing this, with the command:
Sheets("View&quot.Columns("B:M&quot.Select

But I have to type in the M.
I can work out the M in number format with the following code:

Col = 2
While Len(Sheets(&quot;View&quot.Cells(1, Col)) <> 0
Col = Col + 1
Wend

But can I convert that into a Letter so
1 = A
2 = B
3 = C
....
26 = Z
27 = AA

and so on?

Or is there a better way?

 

[SkipVought]

Hi,

If you're looking for the range that has data, you can use the UsedRange method under most circumstances where you are not deleteing data from your sheet...

with activesheet.usedrange
   for iCol = 1 to .columns.count + .column - 1

   next
end with

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Sylv4n]

That would slelect them one at a time but I need them all selected @ once.
I need to run the folloing code to sort the infomation:

Selection.Sort Key1:=Range(&quot;B1&quot, Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight

for this to run it needs all of the Col's selected

 

[SkipVought]

Well your initial question had a loop...

Cells(1,2).Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight

ought to work UNLESS you have empty columns.

If you do them

activesheet.usedrange.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Sylv4n]

No the code did not work, The reason I hd the loop in at rhe start was to fins the Number of the last col.
A Macro produced the following code:

Columns(&quot;B:M&quot.Select
Selection.Sort Key1:=Range(&quot;B1&quot, Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight

But I cant have tghe M in there, it needs to be the last Col used.
Understand?

 

[Bowers74]

Dim Col as integer
dim R as long
Dim c as string
Col = [IV1].end(xltoleft).col
R = [B65536].end(xlup).row
c = cells(lastrow,lastcol).address
range(&quot;B1:&quot; & c).Sort Key1:=Range(&quot;B1&quot, Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight
[/code]

Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[SkipVought]

Your code has it sorting left to right.

Is that what you want?

This code does work is there's data in column M but no data AFTER column M



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Sylv4n]

I see, I do have a couple of strays after M but I wanted them to stay there, I should give them headders as well, Thanks
Bowser74 - Good Idea this I more along the lines

I will have another look tomorow.

Thank you both!

 

[Luis939]

I tried coming up with a solution to your problem, but im affraid i came up short, i was able to get the column in letter format for you, but i still cant get it to select the columns, but maybe you could finish it, hope this helps
but i warn you, it looks rather crude and simple (im still an amateur programmer), now i apologize ahead of time for my silly variable convention, but i was pressed for time...see if this works


columnnumber = Selection.End(xlToRight).Address(, False)
b = InStr(columnnumber, &quot;$&quot
c = Left(columnnumber, b - 1)

&quot;c&quot; is now your column letter, now you can use it

the first step saves you the extra steps and time it would take to run a loop, you can do it in just one step, try the code out, if you're satisfied with it and want to know how it works then let me know ok, hope it helps

 

[DaleWatson123321]

Sylv4n,

Like Skip, I too wonder about sorting Left-To-Right.

I tried Mike's routine, and I think he might have been a bit hasty, as I couldn't get his routine to work.

Here's a revision to his routine. It works, and you'll notice I also changed the orientation.

Sub Sort_Data()
Dim Col As Integer
Dim R As Long
Dim c As String
Col = [IV1].End(xlToLeft).Column
R = [B65536].End(xlUp).Row
c = Cells(R, Col).Address
Range(&quot;B1:&quot; & c).Sort Key1:=Range(&quot;B1&quot, _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End Sub

Hope this helps. Please advise as to how you make out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Bowers74]

You're right Dale, I was a bit hasty. To be honest, I just copied the &quot;Sort&quot; portion of the code from Sylv4n's example, without thinking.

Sorry!


Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Sylv4n]

Yes, Dale that worked It required a bit of tweeking:

Dim Col As Integer
Dim R As Long
Dim c As String
Col = Sheets(&quot;View&quot.[IV1].End(xlToLeft).Column
R = Sheets(&quot;View&quot.[B65536].End(xlUp).Row
c = Cells(R, Col).Address
Sheets(&quot;View&quot.Range(&quot;B1:&quot; & c).Sort Key1:=Sheets(&quot;View&quot.Range(&quot;B1&quot, Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight

Jut really putting the Sheet Name as I am runnig the code from a &quot;settings&quot; Sheet. Also I wanted the diffrent orientation, what was intended, the reason being I have already sorted Col A now I want to sort Row 1.

All I needed was that .Address, that is what I have been looking for, converting two numbers into a cell referance, Perfect.

I would like to thank you all for your help ), some very good ideas here.