Sep 23 2021 07:13 PM
Sep 23 2021 07:13 PM
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
Sep 23 2021 07:35 PM - edited Sep 23 2021 07:37 PM
I broke rule number one in forums: always search first... Think I found my answer.
In the Macro I changed:
Sep 26 2021 03:44 AM
@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.
where 4711 just is high enough to get all the characters.
F2 parses the cell references;
output;firstCellAddress & ":" & lastCellAddress;
.CurrentRegion is just fine and this gives similar possibilities without macros, eg if the table is moved from A1.
Sep 26 2021 04:12 AM - edited Sep 26 2021 04:17 AM
Considering you example:
To get the range you type:
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)
Sep 26 2021 04:29 AM - edited Sep 26 2021 04:41 AM
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
Sep 26 2021 05:24 AM
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
Two cells linking to different files
For version not having the LET function, it is the same as
Sep 26 2021 05:34 AM
Sep 26 2021 05:44 AM