0

I have a table in Excel. It is 2 columns right now with about 50 rows. I want to clear the contents in VBA (I have a macro set up that runs smoothly and populates the table). I want it to be one ROW instead of 2 columns and 50 rows. What code lines do I need for this - I am new to VBA.

Thank you :)

I tried

Dim sTableName As String
Dim sSheetName As String
sSheetName = "VBAMacro"
sTableName = "ProductSale_Table"
Sheets(sSheetName).ListObjects(sTableName).Delete

but this simply removes content. I need to figure out how to clear it AND make it one row.

braX
  • 11,506
  • 5
  • 20
  • 33
webpie
  • 1
  • "Make it one row" does that mean you want to concatinate an entire table? What is the goal... maybe example images may help? – Cyril Nov 11 '22 at 21:01
  • If you want to delete the data, instead of `Sheets(sSheetName).ListObjects(sTableName).Delete`, you could use `With ThisWorkbook.Worksheets(sSheetName).ListObjects(sTableName): If Not .DataBodyRange Is Nothing Then .DataBodyRange.Delete: End With`. The colons denote new lines. – VBasic2008 Nov 12 '22 at 08:51

1 Answers1

0

You should always set-up all Worksheet objects and ListObject(Table).

Try the modified code below:

Dim Tbl As ListObject
Dim sTableName As String
Dim sSheetName As String


sSheetName = "VBAMacro"
sTableName = "ProductSale_Table"

' Set the List-Object
Set Tbl = ThisWorkbook.Worksheets(sSheetName).ListObjects(sTableName)
With Tbl
    .DataBodyRange.ClearContents ' clear contects from Table
        
    .Resize .Range.Resize(1, .Range.Columns.Count) ' resize range (rows and columns) of Table
End With
Shai Rado
  • 33,032
  • 6
  • 29
  • 51
  • In my pre-existing code, where do I add this chunk? Right before "End Sub" at the very end? – webpie Nov 11 '22 at 21:48
  • @webpie take my code and replace it with the part you shared – Shai Rado Nov 11 '22 at 22:07
  • You need 2 instead of 1 because 1 is not possible (`.Range` includes `.HeaderRowRange`). `ClearContents` is a good choice because OP would probably like to keep the formatting. – VBasic2008 Nov 12 '22 at 09:09