Forum Discussion

redhandler's avatar
redhandler
Copper Contributor
Sep 24, 2021

Update Macro to auto capture cell range and table name

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

 

 

 

Macros and VBA

7 Replies

  • redhandler's avatar
    redhandler
    Copper Contributor
    Sep 24, 2021

     

     

    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,

     

     

     

     

    • bosinander's avatar
      bosinander
      Iron Contributor
      Sep 26, 2021

      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.

      • Juliano-Petrukio's avatar
        Juliano-Petrukio
        Bronze Contributor
        Sep 26, 2021

        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)

Resources