CODEWITHSUNDEEP
vbaexcelborder
Pythonist
Pythonist

Reputation: 109

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

Upvotes: 0

Views: 876

Answers (2)

ASH
ASH

Reputation: 20302

To find the last used row, try this.

Sub FindingLastRow()

'PURPOSE: Different ways to find the last row number of a range
'SOURCE: www.TheSpreadsheetGuru.com

Dim sht As Worksheet
Dim LastRow As Long

Set sht = ThisWorkbook.Worksheets("Sheet1")

'Ctrl + Shift + End
  LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

'Using UsedRange
  sht.UsedRange 'Refresh UsedRange
  LastRow = sht.UsedRange.Rows(sht.UsedRange.Rows.Count).Row

'Using Table Range
  LastRow = sht.ListObjects("Table1").Range.Rows.Count

'Using Named Range
  LastRow = sht.Range("MyNamedRange").Rows.Count

'Ctrl + Shift + Down (Range should be first cell in data set)
  LastRow = sht.Range("A1").CurrentRegion.Rows.Count

End Sub

Also, to find the last used column, check this out.

Sub FindingLastColumn()

'PURPOSE: Different ways to find the last column number of a range
'SOURCE: www.TheSpreadsheetGuru.com

Dim sht As Worksheet
Dim LastColumn As Long

Set sht = ThisWorkbook.Worksheets("Sheet1")

'Ctrl + Shift + End
  LastColumn = sht.Cells(7, sht.Columns.Count).End(xlToLeft).Column

'Using UsedRange
  sht.UsedRange 'Refresh UsedRange
  LastColumn = sht.UsedRange.Columns(sht.UsedRange.Columns.Count).Column

'Using Table Range
  LastColumn = sht.ListObjects("Table1").Range.Columns.Count

'Using Named Range
  LastColumn = sht.Range("MyNamedRange").Columns.Count

'Ctrl + Shift + Right (Range should be first cell in data set)
  LastColumn = sht.Range("A1").CurrentRegion.Columns.Count

End Sub

Upvotes: 0

yk11
yk11

Reputation: 768

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)

Upvotes: 1

Related Questions