MS Excel - Selecting a range using the last cell

[chris24]

Hi! Can you help with this problem... it may seem really simple, but I'm new to VB so it's proving to be a little tricky (for me!). Basically the problem is this; using code I've written a VB macro that will present data in a more "visually friendly" format. This is done in Excel. The only thing I'm having problems with is border style. The thing is the macro is to be used on many different sources of data and there will never always be the same number of records (rows) in the spreadsheet each time. Therefore, what I'm trying to do is select all of the cells up to and including the last cell and then format the cells so that they have a border. At the moment I've had to define a range and then afterwards I manually delete the excess cells. Is this possible?

If you can help, I'd be very grateful... not to mention relieved in not having to manually delete cells I don't need.

Thanks.

[Stan]

Perhaps this little snippet may help you get headed in the right direction.

Sub FormatBorders()

Dim lRows As Long
Dim lColumns As Long
Dim lCountc As Long
Dim rngArea As Range

StartRow = 1     'You can start at any row or column you want.
StartColumn = 1  'If you always use the same starting point
                 'then ignore these two lines and enter a value
                 'for them in the code below
With ActiveSheet
    lRows = .UsedRange.Rows.Count
    lColumns = .UsedRange.Columns.Count
End With

For lCountc = StartColumn To lColumns
    Set rngArea = Range(ActiveSheet.Cells(StartRow, lCountc), ActiveSheet.Cells(lRows, lCountc))
    rngArea.Borders.Color = &H3
Next lCountc
    
End Sub

The key to this code (which I have found very useful) is the With ActiveSheet... EndWith which nicely determines the row x column size of your file.

Hope it helps.

[Manal]

You can make use of the SpecialCells

Cells.SpecialCells(xlCellTypeLastCell)

Good luck
Manal

[InvisibleDuncan]

Using this:

ActiveWorkbook.ActiveSheet.UsedRange.Select

... will select the whole used range. Note that the used range doesn't necessarily start at A1 but (as the name implies ) at the first cell you've used. You can then do whatever you want with the formatting.