Hi Michael,
It's not a corrupted sheet.
Based on all the comments, some info and explanations for you :
1. If you have one or more blanks in a cell, then the cell value has a LENGTH and then VBA code
If <range> = "" will FAIL.
2. The reason in that situation that the column
filter for excluding blanks still works in that situation is because Excel considers "" (empty) , " " (single blank), " " (multiple blanks) to be logically equivalent
for filtering purposes. But VBA is more precise and considers one or more blanks is not equal to the empty string "".
3. Therefore, great that the filter solved your 2nd problem of "
I want cells that look empty to be hidden" , but for your future benefit (and the benefit of others who might read this somewhat painful thread) you can eliminate such rows (or in your case make them PASS your if test, by changing your original code very slightly :
VBA Code:
If Trim(c.Value) = "" Then
The TRim() drops all the extra spaces so you end up with the empty string
4. HOWEVER, the more likely cause of the main problem ("
Why is this hiding ALL the rows instead of just the empty ones ?" is that the cells that you
think the code is walking between A4 and A50 are not on the Target Worksheet.
I had this issue in a recent macro where I defined a range something like you do ("A4:A50") and even though *I* though that the currently visible sheet was the Active Sheet (and thus the owner of the Range) Excel decided that the range was on some other sheet in another workbook that I also had open. Therefore to avoid this possible ambiguity, just make sure to explicitly identify the cells you are aiming to process, something like this :
VBA Code:
Sub hiderows()
Dim c As Range
Worksheets("Layout").Rows("4:50").EntireRow.Hidden = False
'rehide the blanks
For Each c In Worksheets("Layout").Range("a4:a50")
If Trim(c.Value) = "" Then
c.EntireRow.Hidden = True
Else
'do nothing, can resinstate the command below, but not required
c.EntireRow.Hidden = False
End If
Next
End Sub
Another way to write this is :
VBA Code:
Sub hiderows()
Dim c As Range
With Worksheets("Layout") [B] [/B]
.Rows("4:50").EntireRow.Hidden = False '<<< NOTICE The "." added before "Rows" here
'rehide the blanks
For Each c In .Range("a4:a50") '<<< NOTICE The "." added before "Range" here
If Trim(c.Value) = "" Then
c.EntireRow.Hidden = True
Else
'do nothing, can resinstate the command below, but not required
c.EntireRow.Hidden = False
End If
Next
End With
End Sub
What probably happened to you is that when you single-stepped the macro you
started on the Layout sheet and kept flicking back to it to check whether lines were being hidden, which meant you were definitely processing the cells A4:A50 on the "Layout" sheet.
But when you run the macro from wherever it is run in the midst of your other Macros you might well be processing the empty cells A4 to A50 on some other sheet - that's why all the cells kept hiding.
This is also very possible if you had multiple workbooks open each with a "Layout" sheet.
5. Another way you could have checked that would have been to add a debug statement to your code like this :
VBA Code:
Sub hiderows()
Worksheets("Layout").Activate
Dim c As Range
Rows("4:50").EntireRow.Hidden = False
'rehide the blanks
For Each c In Range("a4:a50")
If Trim(c.Value) = "" Then
Debug.print "About to hide Row " & c.Row & " on Sheet " & c.WorkSheet.Name & " in workbook " & c.Worksheet.Parent.Name
c.EntireRow.Hidden = True
End If
Next
End Sub
Then after your macro is run you check the output in the Immediate window (Ctrl-G) :
About to hide Row 3 on Sheet Layout in workbook Book43
About to hide Row 4 on Sheet Layout in workbook Book43
About to hide Row 5 on Sheet Layout in workbook Book43
About to hide Row 8 on Sheet Layout in workbook Book43
About to hide Row 9 on Sheet Layout in workbook Book43
...
Hope that helps clear things up for you (and makes you ultimately a better macro programmer !
Cheers,
Warren.
