Selecting a range of cells is not working 1

[willyboy58]

I am trying to select a range of cells for left justifying. The columns are from A to D with all the rows in these columns. When I run the following procedure, the only cell that gets left justified is the very last cell in column A.
I have tried with the range ("A&quot as well as ("A11&quot in the pro below. What am I missing?

TIA, Bill

Sub LeftJustifyCells()

ActiveSheet.Range("A11&quot.End(xlDown).Select
MsgBox "should select cells in range"

With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With

Range("A1&quot.Select

End Sub

 

[TonyJollans]

Hi willyboy58,

You would be better off asking code questions in the VBA forum. Although plenty people here do know VBA it makes it more confusing for others who come along after.

To answer your question. ActiveSheet.Range("A11&quot.End(xlDown) finds a single cell below the activecell in range A1..D1 (cell A1 from what you say) - it might be the one you want (depending on whether your range contains empty cells). What you want to do (I think) is find the last non-blank cell in column D and select the range from A1 to that cell. There are several ways to do that, here's one:

ActiveSheet.Range("A1", ActiveSheet.Range("D65536").End(xlup)).Select

Enjoy,
Tony

 

[willyboy58]

Thanks Tony,

Didn't know that there was a VBA board. Have bookmarked it.

Here is another way that I just came across.

ActiveSheet.Range("A11&quot.EntireColumn.Select


Bill

 

[memberlogin]

how can I run a macro bsed on which cell has been selected and using the contents of the cell to summaries all occurances of Cell contents choice!

 

[memberlogin]

How can i run s macro when a cell is selected that is based on the contents of the cell. e.g. summarize in another sheet totals of the name of the cell

 

[TonyJollans]

Hi memberlogin,

Please start a new thread with a new question - don't piggyback on someone else's - it just causes confusion.

That said, I don't really understand your question but to run a macro based on cell selection you need to use the Worksheet_SelectionChange event. If you need more detail, I (or someone else) will see you in a new thread.

Enjoy,
Tony