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

 

[t0ny84]

Try

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

For Each c In Range("A2:A8")
    With Sheets("Drops2").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

t0ny84

 

[sspatriots]

Try

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

For Each c In Range("A2:A8")
    With Sheets("Drops2").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

t0ny84

It tells me the subscript is out of range now.

 

[t0ny84]

Round 2, sorry I copied the wrong code.

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

  For Each c In Range("A2:A8")
    With Sheets("Drops2").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

 

[Peter_SSs]

I had the formula below

Regarding the above and your thread title:

For the future, so you get the right people looking at your thread, what you have posted is not a formula, it is a macro or a vba procedure. Some forum helpers only want to help with vba/macro questions & that is who you need to help you with questions like this. Many of those 'macro helpers' will simply bypass your thread because the thread title indicates it is a formula question, which it is not.

 

[sspatriots]

Regarding the above and your thread title:

For the future, so you get the right people looking at your thread, what you have posted is not a formula, it is a macro or a vba procedure. Some forum helpers only want to help with vba/macro questions & that is who you need to help you with questions like this. Many of those 'macro helpers' will simply bypass your thread because the thread title indicates it is a formula question, which it is not.

Noted. It was a mistake in haste to get it posted before I had to leave the office. Thanks

 

[sspatriots]

Try

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

For Each c In Range("A2:A8")
    With Sheets("Drops2").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

t0ny84

This is genius. Thank you for your help. SS

 

[sspatriots]

I have one more question. Is it possible to get this code to re-size and exclude any rows where a cell in the first column is blank?

 

[t0ny84]

I have updated the first portion of the code. This should check the value of C and if it's blank then move to next row. If not blank then it goes through normal code.

If C.value = "" then
Next C
End If

Sub Clear_And_Resize_Tables_v2()
  Dim c As Range
  For Each c In Range("A2:A14")

If C.value = "" then
Next C
End If

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



For Each c In Range("A2:A8")

    With Sheets("Drops2").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 have updated the first portion of the code. This should check the value of C and if it's blank then move to next row. If not blank then it goes through normal code.

If C.value = "" then
Next C
End If

Sub Clear_And_Resize_Tables_v2()
  Dim c As Range
  For Each c In Range("A2:A14")

If C.value = "" then
Next C
End If

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



For Each c In Range("A2:A8")

    With Sheets("Drops2").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

Thank you. I will add this to my code first thing Monday. Much appreciated. Have a great weekend.