asked on

Format Table via VBA

Sub Macro1()
    
    Dim lEndrow As Long, rng1 As Range
    lEndrow = Sheets("Payroll Summary").Range("A" & Sheets("Pivots").Rows.Count).End(xlUp).Row
   
    Set rng1 = Sheets("Payroll Summary").Range("A1:lEndRow")
    ActiveSheet.ListObjects("rng1").TableStyle = "TableStyleMedium1"
    
End Sub

Open in new window

Hi,

I have the above macro designed and some what toyed with :o

When I run it, it gives an error (as expected)

Set rng1 = Sheets("Payroll Summary").Range("A1:lEndRow")

I really suspect the error is at the end. Could anyone please help me integrate the last row into this formatting?

Much appreciated!

Shanan212

ASKER

Even when the range is correct in other way, the next line gives error 'subscript' out of range

I used this to test it out (but I do want to define the end of range

Set rng1 = Sheets("Payroll Summary").Range("A1:C99")

SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Shanan212

ASKER

That solves the range error but I think I am defining a range and then treating it as a table.

In that case, how would I define a range as a table?

Much appreciated!

ASKER CERTIFIED SOLUTION

dlmille

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Norie

What exactly do you mean by a table?

If you actually want to format a listobject you just need it's name, you don't need to define it's range.

I'm pretty sure that name isn't 'rng' which you are using in the code, and is what is causing the error.

You need something like this:

ActiveSheet.ListObjects(<TableName>).TableStyle = "TableStyleMedium1"

Replace <TableName> with the name of the table.

If there's only one table on the sheet you can use ListObjects(1).

dlmille

The code I provided creates the table - from that point, you can use the table's name to affect changes, as imnorie as suggested.

Shanan212

ASKER

Imnorie,

I could do that but the table get deleted every time I run another macro. So I have to 'define' a table on VBA side and then assign a formatting!

Dave,

Quick question,

Where in your coding are you assigning the formatting?

dlmille

I didn't - just went with the defaults.

here's with the formatting:

Sub convertToTable()
Dim wkb As Workbook
Dim sht As Worksheet
Dim tblRng As Range
Dim mytable As Object

    Set wkb = ThisWorkbook
    Set sht = wkb.Sheets("Payroll Summary")
    
    Set tblRng = sht.Range("A1:C" & sht.Range("A" & sht.Rows.Count).End(xlUp).Row)
    
    sht.ListObjects.Add(xlSrcRange, tblRng, , xlYes).Name = "Payroll_Table"
    Set mytable = sht.ListObjects("Payroll_Table")
    mytable.TableStyle = "TableStyleMedium1"
End Sub

Open in new window

Shanan212

ASKER

Thanks! This sort of info is hard to find over internet!

dlmille

Sometimes I just record a macro while I do stuff I'd never coded before. After that, I start googling :)

Dave

Shanan212

ASKER

Yup! I do that too :)

Norie

If you don't know the name of the table and it is the only one on the activesheet you can use ListObjects(1).