Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a Range
Hello all,
I am trying to use the following code (courtesy of Tom Ogilvy) to colour the range from B26 to the last occupied cell without success. You will note that I have named the last occupied cell 'Lastcell' thinking this will help with selection and delete it at the end as I need to use it with different sheets in the same workbook. Where am I going wrong? Sub GetRealLastCell() Dim RealLastRow As Long Dim RealLastColumn As Long Range("A1").Select On Error Resume Next RealLastRow = _ Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row RealLastColumn = _ Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column Cells(RealLastRow, RealLastColumn).Select ActiveWorkbook.Names.Add Name:="LastCell" Range("b26:LastCell").Select With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid End With ActiveWorkbook.Names("lastcell").Delete End Sub TIA |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a Range
Range("b26:LastCell").Select
should be Range(Range("b26") , Range("LastCell")).Select -- Regards, Tom Ogilvy "Karen" wrote in message om... Hello all, I am trying to use the following code (courtesy of Tom Ogilvy) to colour the range from B26 to the last occupied cell without success. You will note that I have named the last occupied cell 'Lastcell' thinking this will help with selection and delete it at the end as I need to use it with different sheets in the same workbook. Where am I going wrong? Sub GetRealLastCell() Dim RealLastRow As Long Dim RealLastColumn As Long Range("A1").Select On Error Resume Next RealLastRow = _ Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row RealLastColumn = _ Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column Cells(RealLastRow, RealLastColumn).Select ActiveWorkbook.Names.Add Name:="LastCell" Range("b26:LastCell").Select With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid End With ActiveWorkbook.Names("lastcell").Delete End Sub TIA |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a Range
Tom
Thank you for your reply. I tried it out but the colouring applied to only the last cell instead of the Range. Karen "Tom Ogilvy" wrote in message ... Range("b26:LastCell").Select should be Range(Range("b26") , Range("LastCell")).Select -- Regards, Tom Ogilvy "Karen" wrote in message om... Hello all, I am trying to use the following code (courtesy of Tom Ogilvy) to colour the range from B26 to the last occupied cell without success. You will note that I have named the last occupied cell 'Lastcell' thinking this will help with selection and delete it at the end as I need to use it with different sheets in the same workbook. Where am I going wrong? Sub GetRealLastCell() Dim RealLastRow As Long Dim RealLastColumn As Long Range("A1").Select On Error Resume Next RealLastRow = _ Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row RealLastColumn = _ Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column Cells(RealLastRow, RealLastColumn).Select ActiveWorkbook.Names.Add Name:="LastCell" Range("b26:LastCell").Select With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid End With ActiveWorkbook.Names("lastcell").Delete End Sub TIA |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a Range
Try it this way:
Sub GetRealLastCell() Dim RealLastRow As Long Dim RealLastColumn As Long Range("A1").Select RealLastRow = _ Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row RealLastColumn = _ Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column With Range(Range("b26"), _ Cells(RealLastRow, RealLastColumn)).Interior .ColorIndex = 6 .Pattern = xlSolid End With End Sub -- Regards, Tom Ogilvy "Karen" wrote in message om... Tom Thank you for your reply. I tried it out but the colouring applied to only the last cell instead of the Range. Karen "Tom Ogilvy" wrote in message ... Range("b26:LastCell").Select should be Range(Range("b26") , Range("LastCell")).Select -- Regards, Tom Ogilvy "Karen" wrote in message om... Hello all, I am trying to use the following code (courtesy of Tom Ogilvy) to colour the range from B26 to the last occupied cell without success. You will note that I have named the last occupied cell 'Lastcell' thinking this will help with selection and delete it at the end as I need to use it with different sheets in the same workbook. Where am I going wrong? Sub GetRealLastCell() Dim RealLastRow As Long Dim RealLastColumn As Long Range("A1").Select On Error Resume Next RealLastRow = _ Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row RealLastColumn = _ Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column Cells(RealLastRow, RealLastColumn).Select ActiveWorkbook.Names.Add Name:="LastCell" Range("b26:LastCell").Select With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid End With ActiveWorkbook.Names("lastcell").Delete End Sub TIA |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a Range
Tom
Thank you very much. Perfect! Karen "Tom Ogilvy" wrote in message ... Try it this way: |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting range in list of range names depending on a cell informa | Excel Discussion (Misc queries) | |||
Selecting A Range | Excel Programming | |||
Selecting A Range | Excel Programming | |||
Selecting A Range | Excel Programming | |||
selecting a range | Excel Programming |