help deciphering code

[chookers]

Colo has given me some code which I have also used one line of before. Embarrassed to ask but can someone help me decipher it so I can use it on my own next time and know what it means!!

Thank you so much -

Here it is...

With Range ([A5], [A65536].End (xlUp))

Since I am working with A5 and below, I have that one sorted, but what does the rest of it do exactly, especially the [A65536]?

Thanks everyone!

[RET79]

On 2002-05-21 20:04, chookers wrote:
Colo has given me some code which I have also used one line of before. Embarrassed to ask but can someone help me decipher it so I can use it on my own next time and know what it means!!

Thank you so much -

Here it is...

With Range ([A5], [A65536].End (xlUp))

Since I am working with A5 and below, I have that one sorted, but what does the rest of it do exactly, especially the [A65536]?

Thanks everyone!

It selects your range. The range starts at cell A5 and the end of the range is this [A65536].End (xlUp) thing.

Now for [A65536].End (xlUp), you might not have realised but an excel spreadsheet is only as big as 65536 rows and 256 columns (I think) so what that does is selects the cell in the bottom row of your column and the End(xlUp) takes it up to the first non blank cell.

Try it yourself, click on cell A65536 then press Ctrl&Up (which does the End(xlUp) bit). That's what it does!

RET79

[ This Message was edited by: RET79 on 2002-05-21 20:15 ]

[chookers]

oh thank you so much! Yes ok that makes perfect sense. I could not for the life of me work out the A65536!

ok so I am beginning to understand how it's used. Would this be the kind of thing you'd use to select the 'last row'? For example let's say you had a sheet which may not have the same number of rows everytime someone opens it, but you wanted them to be able to make all the font arial. Would you just do something like use the A65536 code and then, what, like this:

With Selection.Font
.Name=Arial
.Size=9
.ColorIndex=0

So basically the A655356 actually has selected it already? It can't possibly be that simple, can it?

My God, that's just excellent (am easily impressed!)

Thanks RET79!

[NateO]

It's not quite that simple, but almost.

First,
You need to close the following:

With Selection.Font
.Name=Arial
.Size=9
.ColorIndex=0
end with 'close With Statements

Second,
65536 is the last row in a normal Excel worksheet. By performing an end(xlup) you've programatically performed the same operation as clicking on a65536 and pressing end then the up arrow, it effectively selects the last row with data in the sheet.

Colo has shown you how to select a5 and the last row of entered data in column A.

Hope this helps.


_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-21 20:42 ]

[chookers]

Nate, Yeah sorry - forgot to close it!!

Ok so I just tested it out, got row A to change, then tried it for a few columns. Here's what I wrote:

Sub Macro1() '
' Macro1 Macro
' Macro recorded 22/05/2002 by The Principal


'
With Range([A5:D5], [A65536:D65536].End(xlUp))

.Font.Size = 20
.Font.ColorIndex = 0
End With

'
End Sub

So would that be the correct way to do it for a span of a few columns? (well it worked anyway!)Is there a better way or have I got the hang of it???

Am loving this and will use this one all the time!

Thank you ))))) well that sort of looks like double chins but you get the idea (or a worm or something)

Cheers!!

[Ivan F Moala]

On 2002-05-21 20:59, chookers wrote:
Nate, Yeah sorry - forgot to close it!!

Ok so I just tested it out, got row A to change, then tried it for a few columns. Here's what I wrote:

Sub Macro1() '
' Macro1 Macro
' Macro recorded 22/05/2002 by The Principal


'
With Range([A5:D5], [A65536:D65536].End(xlUp))

.Font.Size = 20
.Font.ColorIndex = 0
End With

'
End Sub

So would that be the correct way to do it for a span of a few columns? (well it worked anyway!)Is there a better way or have I got the hang of it???

Am loving this and will use this one all the time!

Thank you ))))) well that sort of looks like double chins but you get the idea (or a worm or something)

Cheers!!

Thats the Ticket !...good on you The Principle

Are you The Principle!!

[RET79]

You've got the hang of it principal!!

RET79

[chookers]

Oh my God, I should hope a principal wouldn't be such a retard at expressing things! No no, I'm only a toady I'm afraid!

Ok well since I am on such a roll (you must be laughing, I am so behind all of you) anyway I am going to try and sort something out that Colo was helping me with yesterday which works great, but now I think I can get it even better with just a little tweaking. We were working on the basis of it being a worksheet change but I think instead I will do a macro button for it.

Here's what I have that I know does what I want:


Sub TEST()
With Range([A5], [A65536].End(xlUp))
.EntireRow.RowHeight = 90
End With
End Sub

Ok, well that is certainly impressive, isn't it?!!!

Here is what colo has done next (what we are going for with this bit is to border top/bottom of EACH row, in Coumns B:L):

With Cells(.Item(1).EntireRow.Row, 2).Resize(, 11)
.Borders(xlEdgeTop).LineStyle = xlContinuous
End With
With Cells(.Item(.Rows.Count).EntireRow.Row, 2).Resize(, 11)
.Borders(xlEdgeBottom).LineStyle = xlContinuous
End With
End With

Now this was intended as a worksheet change, but what if instead I wanted to apply it to a whole sheet. I could still do it under the A65536 code, but how can I tell it to select each individual row?

In the above code, I understand the 'borders xlEdgeBottom etc line, and am trying to understand the 2 and 11. Does that refer to column B and K(?) or L (?)Also why the need for 'resize' in that line?

Thank you! P.s I am hardly getting any work done with that open letter thing - unreal, I am compelled to keep reading it!!