Home

VBA macro: loop through a set of ranges/clear its content depending on the value of 1 of its cells

%3CLINGO-SUB%20id%3D%22lingo-sub-550388%22%20slang%3D%22en-US%22%3EVBA%20macro%3A%20loop%20through%20a%20set%20of%20ranges%2Fclear%20its%20content%20depending%20on%20the%20value%20of%201%20of%20its%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-550388%22%20slang%3D%22en-US%22%3E%3CH3%20id%3D%22toc-hId-1538575842%22%20id%3D%22toc-hId-1538575871%22%3E%3CSTRONG%3E%3CU%3ESOME%20BACKGROUND%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FH3%3E%3CP%3EI%20have%2012%20.xlsx%20workbooks%2C%20each%20containing%20about%26nbsp%3B1%2C500%20tables%2C%20with%201%20table%20per%20tab.%20Data%20populates%20cells%20C9%3AE56.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEach%20table%20is%2056%20rows%20tall%20and%205%20columns%20wide.%20The%20rows%20are%20as%20follows%3A%3C%2FP%3E%3CUL%3E%3CLI%3ERows%201-6%3A%20Table%20Titles%3C%2FLI%3E%3CLI%3ERow%207%3A%20Column%20Headers%3CUL%3E%3CLI%3EGrade%3C%2FLI%3E%3CLI%3EPerformance%20Level%3C%2FLI%3E%3CLI%3EFrequency%3C%2FLI%3E%3CLI%3ERow%20Percent%3C%2FLI%3E%3CLI%3EColumn%20Percent%3C%2FLI%3E%3C%2FUL%3E%3C%2FLI%3E%3C%2FUL%3E%3CP%3EThe%20remaining%20rows%20occur%20in%20sets%20of%206.%20For%20each%20of%207%20grades%20(plus%20a%20grand%20total%20set%2Frange)%2C%20there%20are%206%20rows%3A%3C%2FP%3E%3CUL%3E%3CLI%3ELevels%201-5%3C%2FLI%3E%3CLI%3ETotal%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CH4%20id%3D%22toc-hId--1210094624%22%20id%3D%22toc-hId--1210094595%22%3E%3CSTRONG%3E%3CU%3ETHE%20ISSUE%2FQUESTION%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FH4%3E%3CP%3EI%20need%20to%20accomplish%20the%20following%3A%3C%2FP%3E%3CUL%3E%3CLI%3EBeginning%20with%20range%20C9%3AE14%3A%3CUL%3E%3CLI%3Eif%20C9%26lt%3B10%2C%20clear%20contents%20in%20C9%3AE9%3B%20same%20for%20rows%2010%3A13%3C%2FLI%3E%3CLI%3Eif%20only%20one%20row%20has%20been%20cleared%2C%20clear%20contents%20for%20C9%3AE13%3C%2FLI%3E%3CLI%3Eif%20C14%26lt%3B10%2C%20clear%20contents%20in%20C9%3AE14%3C%2FLI%3E%3C%2FUL%3E%3C%2FLI%3E%3CLI%3ELoop%20through%20this%20process%20for%20the%20remaining%206%20grades%2Franges%3C%2FLI%3E%3CLI%3EFor%20the%20Grand%20Total%20Range%20(C51%3AE56)%3A%3CUL%3E%3CLI%3Eif%20C51%26lt%3B10%2C%20clear%20contents%20in%20C51%3AE51%3B%20same%20for%20rows%2052%3A55%3C%2FLI%3E%3CLI%3Eif%20only%20one%20row%20has%20been%20cleared%2C%20clear%20contents%20for%20C51%3AE55%3C%2FLI%3E%3CLI%3Eif%20C56%26lt%3B10%2C%20clear%20contents%20of%20C56%3C%2FLI%3E%3C%2FUL%3E%3C%2FLI%3E%3C%2FUL%3E%3CH4%20id%3D%22toc-hId-532715711%22%20id%3D%22toc-hId-532715740%22%3E%26nbsp%3B%3C%2FH4%3E%3CH4%20id%3D%22toc-hId--2019441250%22%20id%3D%22toc-hId--2019441221%22%3E%3CU%3E%3CSTRONG%3ETHE%20REAL%20ISSUE%3C%2FSTRONG%3E%3C%2FU%3E%3C%2FH4%3E%3CP%3EI'm%20incredibly%20new%20to%20VBA%20code%20and%20I'm%20on%20a%20%3CSTRONG%3E%3CEM%3Every%3C%2FEM%3E%26nbsp%3Btight%20deadline%3C%2FSTRONG%3E.%20I%20feel%20like%20I%20could%20figure%20it%20out%20if%20I%20had%20more%20time%2C%20but%20I%20don't.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20wrote%20the%20code%20below%2C%20but%20the%20cell.Value%26nbsp%3Blanguage%20in%20the%20if%20statement%20causes%20problems%20(did%20I%20need%20to%20Dim%20something%20for%20cell%3F).%20I%20have%20a%20sense%20that%20the%20problem%20is%20in%20the%20line%20above%20that.%26nbsp%3BOr%20maybe%20I%20should%20be%20using%20Offset%20or%20something%20else%20entirely%3F%20But%2C%20honestly%2C%20I'm%20grasping%20at%20straws%20at%20this%20point%2C%20so%20I'm%20asking%20for%20your%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20greatly%20appreciate%20any%20insight%20or%20assistance%20anyone%20can%20offer!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20HelpMe()%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BDim%20a%20As%20Range%2C%20b%20As%20Range%2C%20c%20As%20Range%2C%20d%20As%20Range%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BDim%20e%20As%20Range%2C%20fAs%20Range%2C%20g%20As%20Range%2C%20h%20As%20Range%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BDim%20row%20As%20Range%3CBR%20%2F%3E%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BSet%20a%20%3D%20Range(%22%24C9%3AE14%22)%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BSet%20b%20%3D%20Range(%22%24C15%3AE20%22)%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BSet%20c%20%3D%20Range(%22%24C21%3AE26%22)%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BSet%20d%20%3D%20Range(%22%24C27%3AE32%22)%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BSet%20e%20%3D%20Range(%22%24C33%3AE38%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BSet%20f%20%3D%20Range(%22%24C39%3AE44%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BSet%20g%20%3D%26nbsp%3BRange(%22%24C45%3AE51%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BSet%20h%20%3D%20Range(%22%24C52%3AE56%22)%3CBR%20%2F%3E%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BFor%20Each%20Range%20In%20Union(a%2C%20b%2C%20c%2C%20d%2C%20e%2C%20f%2C%20g%2C%20h).Rows%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20If%20cell.Value%20%26lt%3B%2010%20Then%20row.ClearContents%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BNext%20row%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENote%3A%20I%20tried%20to%20format%20the%20code%20as%20code%2C%20but%20got%20a%20message%20that%20it%20was%20invalid%20HTML.%20Apologies!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-550388%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
dawkterjay
Occasional Visitor

SOME BACKGROUND

I have 12 .xlsx workbooks, each containing about 1,500 tables, with 1 table per tab. Data populates cells C9:E56.

 

Each table is 56 rows tall and 5 columns wide. The rows are as follows:

  • Rows 1-6: Table Titles
  • Row 7: Column Headers
    • Grade
    • Performance Level
    • Frequency
    • Row Percent
    • Column Percent

The remaining rows occur in sets of 6. For each of 7 grades (plus a grand total set/range), there are 6 rows:

  • Levels 1-5
  • Total

 

THE ISSUE/QUESTION

I need to accomplish the following:

  • Beginning with range C9:E14:
    • if C9<10, clear contents in C9:E9; same for rows 10:13
    • if only one row has been cleared, clear contents for C9:E13
    • if C14<10, clear contents in C9:E14
  • Loop through this process for the remaining 6 grades/ranges
  • For the Grand Total Range (C51:E56):
    • if C51<10, clear contents in C51:E51; same for rows 52:55
    • if only one row has been cleared, clear contents for C51:E55
    • if C56<10, clear contents of C56

 

THE REAL ISSUE

I'm incredibly new to VBA code and I'm on a very tight deadline. I feel like I could figure it out if I had more time, but I don't.

 

I wrote the code below, but the cell.Value language in the if statement causes problems (did I need to Dim something for cell?). I have a sense that the problem is in the line above that. Or maybe I should be using Offset or something else entirely? But, honestly, I'm grasping at straws at this point, so I'm asking for your help.

 

I would greatly appreciate any insight or assistance anyone can offer!

 

Sub HelpMe()
     Dim a As Range, b As Range, c As Range, d As Range

     Dim e As Range, fAs Range, g As Range, h As Range
     Dim row As Range

     Set a = Range("$C9:E14")
     Set b = Range("$C15:E20")
     Set c = Range("$C21:E26")
     Set d = Range("$C27:E32")
     Set e = Range("$C33:E38")

     Set f = Range("$C39:E44")

     Set g = Range("$C45:E51")

     Set h = Range("$C52:E56")

     For Each Range In Union(a, b, c, d, e, f, g, h).Rows
          If cell.Value < 10 Then row.ClearContents
     Next row
End Sub

 

Thank you!

 

Note: I tried to format the code as code, but got a message that it was invalid HTML. Apologies!

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies