Mod to a formula I had help with for another workbook

sspatriots

Well-known Member
Joined
Nov 22, 2011
Messages
585
Office Version
  1. 365
Platform
  1. Windows
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

VBA 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
End Sub
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Try
VBA 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

t0ny84
 
Upvote 0
Try
VBA 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

t0ny84
It tells me the subscript is out of range now.
 
Upvote 0
Round 2, sorry I copied the wrong code.

VBA 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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Try
VBA 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

t0ny84
This is genius. Thank you for your help. SS
 
Upvote 0
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?
 
Upvote 0
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.


VBA Code:
If C.value = "" then
Next C
End If

VBA Code:
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
 
Upvote 0
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.


VBA Code:
If C.value = "" then
Next C
End If

VBA Code:
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.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top