Update Macro to auto capture cell range and table name

[redhandler]

Hi everyone,

Newbie here. (Office 365, Windows 10 user)

I run a lot of report on Excel files, and want to automatically create and format the cell range as a table and apply a table style.

However the macro I record and want to use for this limits the cell range to the spreadsheet cell range for which I record it.

This cell range will vary for different Excel spreadsheets. 

The other thing it does is include the 'Table2' name (as it was the second document I had open).

Is there an edit I can make to the recorded Macro to automatically capture the cell range and name of table?

Thanks so much.

Sub Report1_Weekly()
'
' Weekly Report
'

'
    Range("A1").Select
    Application.CutCopyMode = False
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$F$6"), , xlYes).Name = _
        "Table2"
    Range("Table2[#All]").Select
    ActiveSheet.ListObjects("Table2").TableStyle = "TableStyleLight8"
    Selection.Rows.AutoFit
    Selection.Columns.AutoFit
End Sub

[#2]

I broke rule number one in forums: always search first... Think I found my answer.

In the Macro I changed:

Range("$A$1:$F$6"),

to:

Range("A1").CurrentRegion,

[#3]

@redhandler Sometimes I need the name and range in the workbook.

Addressing the table and then parse the formula is one way to do it.

G4 =Table1

F4 =MID(FORMULATEXT(G4);2;4711)

where 4711 just is high enough to get all the characters.

F2 parses the cell references;
=LET(table;Table1;
firstCell;INDEX(table;1;1);
lastcell;(INDEX(table;ROWS(table);COLUMNS(table)));
firstCellAddress;ADDRESS(ROW(firstCell);COLUMN(firstCell));
lastCellAddress;ADDRESS(ROW(lastcell);COLUMN(lastcell));
output;firstCellAddress & ":" & lastCellAddress;
output
)

.CurrentRegion is just fine and this gives similar possibilities without macros, eg if the table is moved from A1.

[#4]

Considering you example:
To get the range you type:

=ADDRESS(@ROW(Table1),@COLUMN(Table1),4)&":"&ADDRESS(@ROW(Table1)+ROWS(Table1)-1,@COLUMN(Table1)+COLUMNS(Table1)-1,4)

LET() function is not available to all excel users thats why I developed another way to get the range.

To get the table name:

On G6 Type =Table1[[#Headers],[Text]]
On F6 Type=MID(FORMULATEXT(G6),2;FIND("[",FORMULATEXT(G6))-2)

[#5]

@bosinander 

In case you perfer an UDF and get all possible desired outputs.

Function GetTableInfo(AnyCell As Range, InfoType As Integer) As Variant
    Dim TableOutput As String

    TableOutput = vbNullString    
    On Error Resume Next
    
    Select Case InfoType    
    Case 1 ' Table Name
        TableOutput = AnyCell.ListObject.Name
    Case 2 ' Table Range
        TableOutput = AnyCell.ListObject.DataBodyRange.Address
    Case 3 ' Number of Rows
        TableOutput = AnyCell.ListObject.DataBodyRange.Rows.Count
    Case 4 ' Number of Columns
        TableOutput = AnyCell.ListObject.DataBodyRange.Columns.Count
    Case Else
        TableOutput = "Undefined"
    End Select    
    GetTableInfo = TableOutput
End Function

[#6]

One way to use LET is to make long formulas easier to read. Positive for both the builder in the future and for others using the model. Sep 2021 though, only available in 365.

Using =Table1 is like using =Table1[#Data] and when still easy to read, I prefer short versions.

@Juliano-Petrukio's approach with finding special characters holds more possibilities :) and is among others a way to get the name of linked files.

Two cells linking to different files
='[Open Workbook.xlsx]Sheet1'!$A$1

='C:\Users\bosinander\[Closed Workbook.xlsx]Sheet1'!$A$1

=LET(

string;FORMULATEXT(A1);
first;FIND("'";string);
last;FIND("]";string);
output;MID(string;first+1;last-first-1);
SUBSTITUTE(output;"[";"")
)

For version not having the LET function, it is the same as

=SUBSTITUTE(MID(FORMULATEXT(A1);FIND("'";FORMULATEXT(A1))+1;FIND("]";FORMULATEXT(A1))-FIND("'";FORMULATEXT(A1))-1);"[";"")

Output;

Open Workbook.xlsx
C:\Users\bosinander\Closed Workbook.xlsx

[#7]

I see in your code that you too prefer clean and easy to read coding :)
When user defined functions are not necessary I prefer to keep the solutions inside the worksheets frames and thus often make it easier for the next user not needing to familiar with macros.

[#8]

@redhandler you

Review what is more applicable considering your environment. New formulas, "global" formulas or UDF (Using VBA Code)

@bosinander 

Macro is the last resource to be used. Specially now that excel has tons of different features.

Yes, code must be easy to understand. Thanks