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:
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!
