Search code examples
vbaexcelborder

Border around Dynamic Range


My maximum data range is C34:S64, columns with values are placed from left to the right. Number of columns and rows will vary (but amount of rows will be the same for all columns).

I'm using xlDown and xlToRight to find first blank cell as follows:

Lastrow = Range("C34").End(xlDown).Offset(1, 0)
Lastcol = Range("C34").End(xlToRight).Offset(0, 1)

I'm struggling with putting that informations into border
How can I limit Lastrow and Lastcol to C64 and S34?

My table is surronded with other data, that's how it looks like sheet


Solution

  • To determine the range you want to highlight let's create a couple of helper functions

    Function MyMax(p_x As Integer, p_y As Integer) As Integer
        If p_x >= p_y Then MyMax = p_x Else MyMax = p_y
    End Function
    
    Function GetRange(p_StartRange As Range, p_MaxRow As Integer, p_MaxCol As Integer) As Range
        Dim lRow as Integer
        lRow = MyMax(p_StartRange.End(xlDown).Row, p_MaxRow)
    
        Dim lCol as Integer
        lCol = MyMax(p_StartRange.End(xlToRight).Column, p_MaxCol)
        Set GetRange = Range(Cells(p_StartRange.Row, p_StartRange.Column), Cells(lRow, lCol))
    End Function
    

    And then in your code

    Dim rngData As Range: Set rngData = GetRange(Range("C34"), 64, 19)