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
The remaining rows occur in sets of 6. For each of 7 grades (plus a grand total set/range), there are 6 rows:
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
Note: I tried to format the code as code, but got a message that it was invalid HTML. Apologies!