VBA Convert to Range

[Pinaceous]

Hi All,

Is there a way through VBA that I can change one of my tables by macro to 'Convert to Range'?

Thanks,

Pin

 

[Rick Rothstein]

Is there a way through VBA that I can change one of my tables by macro to 'Convert to Range'?

There does not seem to be a direct way. In order to look for an indirect way, we need to know what is in your table. Are there any formulas in the table? If so, are any of them array-entered formulas? Do you have any other cells anywhere in the workbook that references the table or its cells?

 

[StephenCrump]

Perhaps:

With Worksheets("Sheet1").ListObjects("Table1")
    .TableStyle = ""   'optional
    .Unlist
End With

 

[BrianJN1]

Here may be a partial way to what you want, assuming that you want something returned like: Range("B3:AN20"). ....

This code begins in B3 and looks for the last row and column. It is returning those values in A1 and A2.

Sub MyRange()

    ' Get last column from left that is not blank
    Dim LastCol As Integer
    LastCol = Sheet1.Range("B3").End(xlToRight).Column

    Range("A1").Value = LastCol
    ' Write text to first blank cell in Row 1
    
    Dim LastRow As Integer
    LastRow = Sheet1.Range("B3").End(xlDown).Row
    Range("A2").Value = LastRow

End Sub

The formula is created in Excel and would have to be rewritten in VBA syntax.

=CONCATENATE(IF(A1>26,CHAR(65+(A1-1)-MOD(A1-1,26)-26),""),CHAR(65+MOD(A1-1,26)))

It's not perfect, but it may be a start.

 

[Pinaceous]

Hello Rick!

I actually googled your old post and found this code that you posted there:

Sub MakeAllTablesRegularRanges()
  Dim WS As Worksheet, LO As ListObject
  For Each WS In Worksheets
    For Each LO In WS.ListObjects
      LO.Unlist
    Next
  Next
End Sub

Thank you for it!

 

[Rick Rothstein]

Hello Rick!

I actually googled your old post and found this code that you posted there:

Sub MakeAllTablesRegularRanges()
  Dim WS As Worksheet, LO As ListObject
  For Each WS In Worksheets
    For Each LO In WS.ListObjects
      LO.Unlist
    Next
  Next
End Sub

I had completely forgotten about that code and, in particular, the Unlist method. Thank you for reminding me of it.

 

[Pinaceous]

Not a problem Rick! Thank you for keeping this forum going with all of your posts!