The problem I am facing with adding pivot table to a table is that my source data in the following code. If my reference code keeps changing, how do i format my source data?
SourceData: "Test!R35C1:R86C13" keepps changing Sometime my table lies on row 35, sometimes on row 40 or row 100. How do I make this dynamic?
Like this?
Function table_range()
'return the range of the source table
first_cell = Find_First_Used_Cell()
last_cell = Range_Find_Method(Range(first_cell))
table_range = first_cell + ":" + last_cell
End Function
Function Find_First_Used_Cell()
'modified
'original source: https://www.excelcampus.com/library/find-the-first-used-cell-vba-macro/
'Finds the first non-blank cell in a worksheet.
Dim rFound As Range
On Error Resume Next
Set rFound = Cells.Find(What:="*", _
After:=Cells(Rows.Count, Columns.Count), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
On Error GoTo 0
If rFound Is Nothing Then
MsgBox "All cells are blank."
Else
'MsgBox "First Cell: " & rFound.Address
Find_First_Used_Cell = rFound.Address
End If
End Function
Function Range_Find_Method(rng As Range)
'modified
'original source: https://www.excelcampus.com/library/find-the-first-used-cell-vba-macro/
'Finds the first non-blank cell in a worksheet.
Dim rFound As Range
On Error Resume Next
Set rFound = Cells.Find(What:="*", _
After:=rng, _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)
On Error GoTo 0
If rFound Is Nothing Then
MsgBox "All cells are blank."
Else
'MsgBox "First Cell: " & rFound.Address
Range_Find_Method = rFound.Address
End If
End Function
Now you can simply get the range like this:
Sub print_my_range()
Set my_range = Range(table_range())
MsgBox (my_range.Address)
End Sub
