Convert Table to Range in all sheets but only for a given range

[WojtekP]

Sub TurnTablesIntoRanges()
Dim wks As Worksheet, objList As ListObject
For Each wks In ActiveWorkbook.Worksheets
For Each objList In wks.ListObjects
objList.Unlist
Next objList
Next wks
End Sub

Hello,

A novice here... I have few tables in each workbook in a multiply sheets excel file.
Can you help me to change this VBA above to be able to only convert tables that are in rows let's say: 20-100 and leave other tables in each workbook intact?

Thanks in advice for your ideas.

Best regards,
Wojtek

 

[Domenic]

Try...

Sub TurnTablesIntoRanges()
    Dim wks As Worksheet, objList As ListObject
    For Each wks In ActiveWorkbook.Worksheets
        For Each objList In wks.ListObjects
            If Union(objList.Range, wks.Rows("2:10")).Address = "$2:$10" Then
                objList.Unlist
            End If
        Next objList
    Next wks
End Sub

Note to convert tables that only lie partially within Rows 2 to 10, replace...

            If Union(objList.Range, wks.Rows("2:10")).Address = "$2:$10" Then

with

            If Not Intersect(objList.Range, wks.Rows("2:10")) Is Nothing Then

Hope this helps!

 

[Yongle]

another option for you

Whole of table must be between rows 20 and 100 to be converted

Sub TurnTablesIntoRanges()
    Dim wks As Worksheet, objList As ListObject, F As Long, L As Long
    
    For Each wks In ActiveWorkbook.Worksheets
        For Each objList In wks.ListObjects
            With objList.Range
                F = .Cells(1, 1).Row
                L = F + .Rows.Count - 1
                If F > 19 And L < 101 Then objList.Unlist
            End With
        Next objList
    Next wks
End Sub