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
Highlighted
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
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies