End(xlDown) not working?
 

I am using the following code to select a variable range of cells in a column
for formatting.
ExcelSheet.Application.Range("B12").Select
ExcelSheet.Application.Range(Selection, Selection.End(xlDown)).Select
With ExcelSheet.Application.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
I get a message that says "Object variable or with block variable not set."
I suspect that my syntax may be slightly off somewhere. Any suggestions?

End(xlDown) not working?
 

The Application object doesn't have a range property. Instead, use

Range("B12").Select
Range(Selection, Selection.End(xlDown)).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

But, better would be this, which would work on the active sheet, and
wouldn't cause problems if B12 is the last filled celll in column B:

With Range("B12", Cells(Rows.Count,2).End(xlUp))
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

But better still might be this, which would work on a specific workbook and
worksheet no matter what sheet is active....

With Workbooks("Workbook Name.xls").Worksheets("Sheet Name")
With .Range("B12", Cells(Rows.Count,2).End(xlUp))
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End With

HTH,
Bernie
MS Excel MVP

"RAHokie" wrote in message
...
I am using the following code to select a variable range of cells in a
column
for formatting.
ExcelSheet.Application.Range("B12").Select
ExcelSheet.Application.Range(Selection, Selection.End(xlDown)).Select
With ExcelSheet.Application.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
I get a message that says "Object variable or with block variable not
set."
I suspect that my syntax may be slightly off somewhere. Any suggestions?

End(xlDown) not working?
 

Thanks for the speedy rescue. I will give these suggestions a try to see
which is best suited to my problem. Actually B12 is the initial populated
cell in the column. I want to select B12 and all the continuously populated
cells in the column below B12 for formatting. This is VBA code in a
function attached to a form. I always find it a little tricky to get the
syntax exactly right - Excel isn't my primary skill! Thanks for the help.

"Bernie Deitrick" wrote:

The Application object doesn't have a range property. Instead, use

Range("B12").Select
Range(Selection, Selection.End(xlDown)).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

But, better would be this, which would work on the active sheet, and
wouldn't cause problems if B12 is the last filled celll in column B:

With Range("B12", Cells(Rows.Count,2).End(xlUp))
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

But better still might be this, which would work on a specific workbook and
worksheet no matter what sheet is active....

With Workbooks("Workbook Name.xls").Worksheets("Sheet Name")
With .Range("B12", Cells(Rows.Count,2).End(xlUp))
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End With

HTH,
Bernie
MS Excel MVP

"RAHokie" wrote in message
...
I am using the following code to select a variable range of cells in a
column
for formatting.
ExcelSheet.Application.Range("B12").Select
ExcelSheet.Application.Range(Selection, Selection.End(xlDown)).Select
With ExcelSheet.Application.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
I get a message that says "Object variable or with block variable not
set."
I suspect that my syntax may be slightly off somewhere. Any suggestions?