Excel Macro To Hide Rows

Microsoft Excel 2003 (full product)
September 23, 2010 at 05:50:28
Specs: Windows XP
Hello, I'm trying to write a macro in excel that hides rows based on the values of 2 rows which are in groups of 6 rows. The range of data is I6:BI2819. In which I am looking for values 'Spend' (I6) and 'Forecast' (I7) and want it to check cells S6:S7 for blank cells, if so then hide the 2 rows plus 4 rows underneath (I6:I11) and repeat down to the end of the range.

Also, can this macro change automatically each month as it will only be run once per period in the year to check the next column across for the blank cells (T6:T7) and so on each month/time it is run?

Is there anyone that can decipher my question??

Many thanks in advance of replies.
Jockywilson

edited by moderator: Subject Line Edited To Be Relevant


See More: Excel Macro To Hide Rows

Report •


#1
September 23, 2010 at 14:50:09
I think I understand your question, but I have some questions of my own:

You said:

I am looking for values 'Spend' (I6) and 'Forecast' (I7) and want it to check cells S6:S7 for blank cells, if so then hide the 2 rows plus 4 rows underneath (I6:I11)

In simple terms, does this mean "I want to hide rows 6 - 11 if both S6 & S7 are blank"?

Then you said:

and repeat down to the end of the range.

Does this mean "I want to hide rows 12 - 17 if both S12 & S13 are blank, hide rows 18 - 22 if both S18 & S19 are blank, etc.?

In other words, sets of 6 rows, starting in Row 6, with no blank lines or anything else in between each set?

Finally, you said, more or less:

next month, check the next column across for the blank cells (T6:T7)

Is it as simple as using the System date and determining the month or is there some twist we need to be aware of?

In other words, if Month = Jan Then check Column S, if Month = Feb then check Column T, etc?


Report •

#2
September 24, 2010 at 01:01:21
Sorry for my ambiguity, I tried to explain clearly but am quite new to the whole vba thing. Yes, sets of 6 rows - I want to hide rows 6-11 if both S6 & S7 are blank.

Your second point is also correct - I want to hide rows 12 - 17 if both S12 & S13 are blank, hide rows 18 - 22 if both S18 & S19 are blank. So no blank lines.

Now here is the main problem. My periods (months) do not align to the normal monthly timeframe of 30,31 days. My calendar runs on a weekly basis and each period runs in the sequence of 4,4,5. Therefore the first month in the year is 4 weeks, second is 4 weeks but the third is 5 weeks, continuing in that sequence to the end of the year. This means any new period may not be in a different month (in the gregorian calendar) to when it was last run so I think we cannot use the system date.

So, would it be easier for me to edit the code every time I need to run it to change the column which the code checks for empty cells?

Thanks for your help DerbyDad03!


Report •

#3
September 24, 2010 at 01:06:47
Can I also add, that the first week in my year is the last week in December so we cannot start from 1st January in the sequence of weeks per period.

Not sure if we can write this into the code as it seems quite difficult to me but am quite comfortable in changing the code every time i need to run it!


Report •

Related Solutions

#4
September 24, 2010 at 01:51:12
Your willingness to edit the code each period implies that you know what Column you want to check at any given time.

Rather than editing the code, why not have the code ask the user to enter the column letter each time? That way, when you get promoted or hit the lottery, you won't have to explain to your replacement how to edit VBA code.

While it's possible to include code that checks the week and matches it to your period 4 -4-5 period calendar, code that queries the user is much easier. If there's any reason you'd rather it be completely automatic, let me know.

Option Explicit
Sub HideSomeRows()
 Dim rw As Integer
 Dim colChk As String
'Stop screen from flickering as Rows are hidden
 Application.ScreenUpdating = False
'Get Column Designation from User
  colChk = _
    Application.InputBox("Which column would you like to check?", _
                          Title:="Column Check", Type:=2)
'Exit if user cancels
      If colChk = "False" Then Exit Sub
'Loop through Range
       For rw = 6 To 2819 Step 6
'Check first 2 Rows of designated column
'Hide rows if both are empty
        If Range(colChk & rw) = "" And Range(colChk & rw + 1) = "" Then _
           Range(colChk & rw & ":" & colChk & rw + 5).EntireRow.Hidden = True
       Next
  Application.ScreenUpdating = False
 End Sub


Report •

#5
September 24, 2010 at 02:25:40
Thanks DerbyDad03 but I am getting an Runtime Error 'Type Mismatch' on line:

If Range(colChk & rw) = "" And Range(colChk & rw + 1) = "" Then _

Any ideas?


Report •

#6
September 24, 2010 at 02:33:05
Ah! found the problem. The column that contains the values actually contains formulas linked to other documents. Any way to amend the string?

Cheers


Report •

#7
September 24, 2010 at 04:19:41
I don't see why a formula would cause a Type Mismatch error. It might not hide the row, based on the value, but it shouldn't cause the code to fail.

I just copied the code directly from my post to make sure nothing was lost in translation.

I then put ='C:\Documents and Settings\Derby\My Documents\[Book2.xls]Sheet1'!$G$9 in Column S6.

The formula returned 0 so that section wasn't hidden, since 0 <> "".

I changed the code to this and it then hid the rows.

        If Range(colChk & rw) = 0 And Range(colChk & rw + 1) = 0 Then _
           Range(colChk & rw & ":" & colChk & rw + 5).EntireRow.Hidden = True

You should make that change because I assume you actually want to check for 0, but I doubt that's the problem.

You could also try this, but since I don't have to I don't know why you should have to.

        If Range(colChk & rw).Value = 0 And Range(colChk & rw + 1).Value = 0 Then _
           Range(colChk & rw & ":" & colChk & rw + 5).EntireRow.Hidden = True

If neither of those changes help, we can talk about sending me the file to check.


Report •

#8
September 24, 2010 at 05:13:13
I noticed that the formula part the way down the column was returning an error so updated those lines and ran both sets of code as outlined. Both worked.

Many thanks for your help with this!!!


Report •

#9
September 24, 2010 at 08:57:19
No problem.

I hadn't considered an error as the cause of the Type Mismatch, but I just forced an error in Column S and got the same result.

I'll keep that in my back pocket in case something like that comes up in the future.

Now I thank you!


Report •

#10
October 11, 2010 at 03:39:01
I need another tweak to your first piece of code.

I now need for the Column Check to check a range of columns (say 6), not just the one specified by the user. Can we amend the code to ask for the first and last columns in a range and then return the values as outlined above?


Report •

#11
October 11, 2010 at 08:52:22
re: "Can we amend the code..."


"We" can, if you say please.


Report •

#12
October 13, 2010 at 01:28:48
My apologies. As this is a follow-on request I guess I need to extend the original formalities.

DerbyDad03, please could you assist me with the addiitional request for your piece of code.

Many thanks.


Report •

#13
October 13, 2010 at 04:27:25
It's not "formalities", it's simply politeness.

If you asked a co-worker for something and then 3 weeks late needed something else from them, wouldn't you include a "Please" and/or a "Thank You" along with the new request? You seem like a nice person, so I assume you would. It shouldn't be any different when asking for help in an online help forum, especially when the help is being provided on a voluntary basis.

To clarify your new request, are you asking for this:

1 - The user inputs a Start Column, e.g. S
2 - The user inputs a End Column, e.g. V
3 - The code should check S6, S7, T6, T7, U6, U7, V6 & V7, all of which have to be blank in order for the row to be hidden.

If that's the case, are there limitations on which columns can be entered? What would you want to happen if the user enters A and IV?


Report •

#14
October 14, 2010 at 03:01:15
Sorry, 'formalitites' was the word that came to mind at the time, probably not the best.

To answer 1,2 & 3... Yes.

The columns A-I contain information that will not change, therefore I only need the user to be able to set the Start Column from column J. The End column will only ever be this column +5 and moves across to the right with time.

So... if start column is J, the end column will be O. The next month will be starting at K, ending at P.

Anything else you need?


Report •

#15
October 14, 2010 at 04:48:38
In Response #10 you said:

"Can we amend the code to ask for the first and last columns in a range"

However, in your latest response, you said:

"The End column will only ever be this column +5"

If that is true, then the answer to my questions 2 & 3 would be "No" since Column V is only 4 columns from Column S.

So which is it:

Do you want to check the start column given by the user + 5, every time or do you want to check the start column and end column given by the user, which could be less than (but I assume never more than) 6 columns?

Those are 2 different situations which require different code.



Report •

#16
October 14, 2010 at 05:10:05
This is evolving all the time so apologies if the request is coming across as a bit muddled. Hopefully this should be the final amendment.

I think the Start column +5 idea will be best.


Report •

#17
October 14, 2010 at 20:43:11
Try this version of the code.

The code will ask the user to input the letter designation of the first column in the range. It will then check Rows 6 & 7, 12 & 13, etc. of that column and the 5 subsequent columns (e.g. J:O) If it finds a value in any of those 12 cells, it will not hide that set of 6 Rows.

Since the user is entering a letter, and we can't directly "increment" a letter to check the next column, the code determines the ASCII code for the letter and uses that number as the value for the For-Next loop that moves through the columns.

Option Explicit
Sub HideSomeRows()
Dim rw, colChkCode, colRng As Integer
Dim colChk As String
'Stop screen from flickering as Rows are hidden
Application.ScreenUpdating = False
'Get Column Designation from User
  colChk = _
    Application.InputBox _
        ("Enter the letter of the first column to check", _
                          Title:="Column Check", Type:=2)
'Exit if user cancels
      If colChk = "False" Then Exit Sub
'Change Column letter to number so we can increment it
       colChkCode = Asc(UCase(colChk))
'Loop through Range
       For rw = 6 To 2819 Step 6
'Check first 2 Rows of designated Column through Column + 5
         For colRng = colChkCode To colChkCode + 5
'If any cell is first 2 rows of any column is not empty
'then stop checking and don't hide this set of 6
           If Range(Chr(colRng) & rw) <> "" Or _
              Range(Chr(colRng) & rw + 1) <> "" Then
                 GoTo NotAllBlank
           End If
         Next
'Hide rows if both are empty
 Range(Chr(colChkCode) & rw & ":" & _
       Chr(colChkCode) & rw + 5).EntireRow.Hidden = True
NotAllBlank:
'Loop and check next pair of rows
  Next
 Application.ScreenUpdating = False
End Sub



Report •

#18
October 15, 2010 at 00:31:26
Fantastic, works like a charm!

Many thanks for your patience on this DerbyDad03! Greatly appreciated.


Report •

#19
January 25, 2011 at 07:31:39
DerbyDad03. Sorry for reopening this thread but I am now getting an error on the above code. Namely at:

If Range(Chr(colRng) & rw) <> "" Or _
Range(Chr(colRng) & rw + 1) <> "" Then

I think this is due to the fact that when the code checks the next 6 columns for data, this takes the column numbering that it is checking past Z and into column AA.

Do we now need to change the below section to include column lettering values at single and double lettering or is this something different?:

colChkCode = Asc(UCase(colChk))

Many thanks for all your help with this.


Report •

Ask Question