Get Last Unused Cell
 

I am trying to get the last unused cell so that I can put a borde
around them. This is because my result set in the worksheet is dynami
and can contain more or less values. Right now I have macro hard-codin
the last cell in the range:
Range("A1:H16").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

How do I find he last unused cell in my range cell property? Thanks

--
Message posted from http://www.ExcelForum.com

Get Last Unused Cell
 

Hi

One way :

Look on Debra's site for a dynamic range
http://www.contextures.com/xlNames01.html#Dynamic

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Session101 " wrote in message ...
I am trying to get the last unused cell so that I can put a border
around them. This is because my result set in the worksheet is dynamic
and can contain more or less values. Right now I have macro hard-coding
the last cell in the range:
Range("A1:H16").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
LineStyle = xlContinuous
Weight = xlThin
ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
LineStyle = xlContinuous
Weight = xlThin
ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
LineStyle = xlContinuous
Weight = xlThin
ColorIndex = xlAutomatic
End With

How do I find he last unused cell in my range cell property? Thanks!


---
Message posted from http://www.ExcelForum.com/

Get Last Unused Cell
 

will this do it?

Sub borderusedrng()
ActiveSheet.UsedRange.BorderAround Weight:=xlThick
End Sub

--
Don Guillett
SalesAid Software

"Session101 " wrote in message
...
I am trying to get the last unused cell so that I can put a border
around them. This is because my result set in the worksheet is dynamic
and can contain more or less values. Right now I have macro hard-coding
the last cell in the range:
Range("A1:H16").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
LineStyle = xlContinuous
Weight = xlThin
ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
LineStyle = xlContinuous
Weight = xlThin
ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
LineStyle = xlContinuous
Weight = xlThin
ColorIndex = xlAutomatic
End With

How do I find he last unused cell in my range cell property? Thanks!


---
Message posted from http://www.ExcelForum.com/

Get Last Unused Cell
 

I am trying to get the last unused cell so that I can put a borde
around them. This is because my result set in the worksheet is dynami
and can contain more or less values. Right now I have macro hard-codin
the last cell in the range:
Range("A1:H16").Select

You may use:
1)
With ActiveCell.CurrentRegion
.BorderAround ColorIndex:=xlAutomatic, Weight:=xlThin
.Borders(xlInsideVertical).Weight = xlThin
.Borders(xlInsideVertical).ColorIndex = xlAutomatic
.Borders(xlInsideHorizontal).Weight = xlThin
.Borders(xlInsideHorizontal).ColorIndex = xlAutomatic
End With
This will format the current cells borders
2)

'usually yuo will always have the headers of your work non dinamic
anyway
and you will add new record, I mean rows to it
One of the columns usually is the key, a value is mandatory
say column (A:A) "Name"
usually A is the column to look after

Sub TryThis()
Dim Rg As Range
Set Rg = Range("A4")
With Range(Rg, Cells(Rg.End(xlDown).Row
Rg.End(xlToRight).Column))
.Select
.BorderAround ColorIndex:=xlAutomatic, Weight:=xlThin
.Borders(xlInsideVertical).Weight = xlThin
.Borders(xlInsideVertical).ColorIndex = xlAutomatic
.Borders(xlInsideHorizontal).Weight = xlThin
.Borders(xlInsideHorizontal).ColorIndex = xlAutomatic
End With

End Su

--
Message posted from http://www.ExcelForum.com