Excel VBA Tables

[radonwilson]

I have written my questions on screenshot, please have a look.

Sub cleanup()

Dim tbl As Range
Dim ws As Worksheet
Set tbl = Range("a1").CurrentRegion
Set ws = ActiveSheet

ws.Name = "Transactions"
Rows("1:10").Delete


Range("D:D").Select
Selection.Cut
Range("A:A").Select
Selection.Insert

Range("C:E").Select
Selection.Insert

Range("B:B").Select
Selection.TextToColumns Destination:=Range("B1"), DataType:=xlFixedWidth

Range("C:E").Delete

    Cells.Replace "Electronic Transactions (Credit Card/Net Banking/GC)", "ET"
    Cells.Replace "Cash On Delivery Transactions and Non-Transactional Fees", "COD"
   
tbl.Select


Selection.WrapText = False
ws.ListObjects.Add(xlSrcRange, tbl, , xlYes).Name = "trans"
ws.ListObjects("trans").TableStyle = ""
ws.ListObjects("trans").HeaderRowRange.Font.Bold = 1

End Sub

 

[Fluff]

Please ask your question in your post & not via an image on a share site. Thanks.

 

[radonwilson]

1. I have created a variable tbl as Range and set it to - tbl=Range("a1").current region. Given VBA code is not including Column A in table trans. Is it happening because I had cut/insert a Column D before Column A? I know this can be fixed by writing tbl.currnetregion, while creating a table. But I want to know how its not working.

VBA Code:
Sub cleanup()

Dim tbl As Range
Dim ws As Worksheet
Set tbl = Range("a1").CurrentRegion
Set ws = ActiveSheet

ws.Name = "Transactions"
Rows("1:10").Delete


Range("D:D").Select
Selection.Cut
Range("A:A").Select
Selection.Insert

Range("C:E").Select
Selection.Insert

Range("B:B").Select
Selection.TextToColumns Destination:=Range("B1"), DataType:=xlFixedWidth

Range("C:E").Delete

Cells.Replace "Electronic Transactions (Credit Card/Net Banking/GC)", "ET"
Cells.Replace "Cash On Delivery Transactions and Non-Transactional Fees", "COD"
  
tbl.Select


Selection.WrapText = False
ws.ListObjects.Add(xlSrcRange, tbl, , xlYes).Name = "trans"
ws.ListObjects("trans").TableStyle = ""
ws.ListObjects("trans").HeaderRowRange.Font.Bold = 1

End Sub

2. I am unable to create a table this way, do I have to give table style separately?

ws.ListObjects.Add(xlSrcRange, tbl, , xlYes,,"").Name = "trans"

 

[Alex Blakenburg]

Given VBA code is not including Column A in table trans. Is it happening because I had cut/insert a Column D before Column A? I know this can be fixed by writing tbl.currnetregion, while creating a table. But I want to know how its not working.

You are correct, Set tbl initially starts at column "A" but when you insert a column before "A" the range of tbl moves to the right.

If you add some debug lines to your code you will see this in the immediate window for tbl.address

Before Inserting Column A
$A$1:$E$20
After Inserting Column A
$B$1:$E$20

I added this debug.print as follows:-

    Range("D:D").Select
    Selection.Cut
  
        Debug.Print "Before Inserting Column A"
        Debug.Print tbl.Address
    Range("A:A").Select
    Selection.Insert
        Debug.Print "After Inserting Column A"
        Debug.Print tbl.Address

Since you not using the tbl object until right at the end, perhaps move your Set tbl = Range("a1").CurrentRegion closer to the end.

2. I am unable to create a table this way, do I have to give table style separately?

I can't find a way of using the None ("") style in the same line as the .Add command.
You might want to consider doing it this way.

    With ws.ListObjects.Add(xlSrcRange, tbl, , xlYes)
        .Name = "trans"
        .TableStyle = ""
        .HeaderRowRange.Font.Bold = 1
    End With