Mod to a formula I had help with for another workbook

[sspatriots]

I had the formula below that I want to modify from a different workbook. I would like to tweak it to accommodate two worksheets for another workbook now. For example, the new workbook sheet names could be "Drops1" and "Drops2" and maybe I would like to specify a different range of "A2:A8" for "Drops2". I tried just copying the first part over and putting it below after where it says "Next c" using the new worksheet names and ranges, but got an error message that said "Run-time error '9': Subscript out of range". Thanks in advance for any assistance, SS

Sub Clear_And_Resize_Tables_v2()
  Dim c As Range
  
  For Each c In Range("A2:A14")
    With Sheets("Drops").ListObjects(c.Value)
      If WorksheetFunction.CountA(.Range) > .Range.Columns.Count Then .DataBodyRange.ClearContents
      .Resize (.Range.Resize(c.Offset(, 1).Value))
    End With
  Next c
End Sub

 

[sspatriots]

I did try this just now. I get a Compile error message that says Next without For.

 

[Peter_SSs]

I did try this just now. I get a Compile error message that says Next without For.

Yes, that definitely is not valid code as it stands.

The original code looks familiar.
Give this modified version a try with a copy of your workbook. You can include as many worksheets/ranges as you want by modifying the 'SheetRanges' line that is commented.

Sub Clear_And_Resize_Tables_v3()
  Dim c As Range
  Dim SheetsRanges As Variant
  Dim i As Long
  
  SheetsRanges = Split("Drops|A2:A14|Drops2|A2:A8", "|")  '<- Add as many sheets/ranges as you want
  
  For i = 0 To UBound(SheetsRanges) Step 2
    With Sheets(SheetsRanges(i))
      For Each c In .Range(SheetsRanges(i + 1))
        If c.Value <> "" Then
          With .ListObjects(c.Value)
            If WorksheetFunction.CountA(.Range) > .Range.Columns.Count Then .DataBodyRange.ClearContents
            .Resize (.Range.Resize(c.Offset(, 1).Value))
          End With
        End If
      Next c
    End With
  Next i
End Sub

 

[t0ny84]

Yes, that definitely is not valid code as it stands.

The original code looks familiar.
Give this modified version a try with a copy of your workbook. You can include as many worksheets/ranges as you want by modifying the 'SheetRanges' line that is commented.

Sub Clear_And_Resize_Tables_v3()
  Dim c As Range
  Dim SheetsRanges As Variant
  Dim i As Long
 
  SheetsRanges = Split("Drops|A2:A14|Drops2|A2:A8", "|")  '<- Add as many sheets/ranges as you want
 
  For i = 0 To UBound(SheetsRanges) Step 2
    With Sheets(SheetsRanges(i))
      For Each c In .Range(SheetsRanges(i + 1))
        If c.Value <> "" Then
          With .ListObjects(c.Value)
            If WorksheetFunction.CountA(.Range) > .Range.Columns.Count Then .DataBodyRange.ClearContents
            .Resize (.Range.Resize(c.Offset(, 1).Value))
          End With
        End If
      Next c
    End With
  Next i
End Sub

@Peter_SSs
Thank you for your answer to this question as I have also learnt! E.g. Sheet Ranges Split.

Reviewing my answer I now see a few things which would cause it to error out. A lesson for me to review responses before posting.

t0ny84

 

[Peter_SSs]

A lesson for me to review responses before posting.

Yes, always a good idea to test code before posting if possible. However, even clicking Debug -> Compile VBA Project in the vba window would have immediately shown a syntax error in that post 9 code.

 

[sspatriots]

Thank you all for the feedback. This is the first I think I've seen the "split" used in code, unless it was buried in some other code somewhere before I never paid much attention to it. I will certainly run this and see how it goes.

 

[Joe4]

sspatriots,
If a reply does not work for you, please do not mark the post as the solution (you had marked the first reply as the solution, but then responded that you got errors when you run it).
Only mark a post as the solution if it correctly answers your original question, and the matter is resolved.
Marking a post as the solution is a way of telling others that the issue has been resolved.

I have unmarked it for you.