HideRows for SPECIFIC Range

[CorrieAnn Gillen]

I "borrowed" this snippet from online, however I need to alter it so that it only hides rows within my range: A36:A125. I tried changing the Rows.Count parameter within, but that didn't work. Suggestions?

Sub HideRows()
On Error Resume Next
ltrw = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To ltrw
 If Cells(i, 1).Value = 0 Then
  Cells(i, 1).EntireRow.Hidden = True
  End If
  Next i
End Sub
 
 
 
Sub UnhideRows()
On Error Resume Next
ltrw = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To ltrw
 If Cells(i, 1).Value = 0 Then
  Cells(i, 1).EntireRow.Hidden = False
  End If
  Next I
End Sub

 

[Smallman]

Hi CorrieAnn

I would do this slightly differently.

I am not sure what your data looks like above line 36 but this might be an option

Sub Hide()
    [A35:A125].AutoFilter 1, "<>0", , , 0
End Sub
 
Sub Unhide()
    [A35:A125].AutoFilter
End Sub

If you want to treat one line at a time (the slow way) can fix that for you as well.

This is the slow train;

Sub HideIt1()  'Excel VBA to hide columns based on criteria Part 3.
Dim rng As Range
    For Each rng In [A36:A125]
        If rng = 0 Then
            rng.EntireRow.Hidden = Not (rng.EntireRow.Hidden)
        End If
    Next
End Sub
 
Sub Unhide1()
[A36:A125].EntireRow.Hidden = False
End Sub

Take care

Smallman

 

[CorrieAnn Gillen]

Smallman,

Thank you for your assistance. I tried these, but they don't provide the desired outcome.

I have a user form that contains several areas requiring input. However, it accommodates for scenarios where there "may" be a need for 50 records, but most times the user only needs (let's say) 10. So, for any particular section I need to provide the option for them to either delete the surplus rows, or even the entire section if not applicable. All without impacting other areas of the spreadsheet. This is why I need to be able to control the range of the code behind the control.

 

[Smallman]

Hi CorrieAnn

I did not provide a file to show how the above worked so my bad. You said in post three you want to delete the rows. I thought you just wanted them hidden.

I will provide a file but a file from you would be more valuable as I will make certain assumptions.

The file uses truncated ranges but you should get the idea.

Take care

Smallman

 

[CorrieAnn Gillen]

Smallman,

Yes, this does what I need it to do. Sorry for adding confusion to my post by using "delete" and "hide" interchangeably. What I really mean is "hide".

I played around with your spreadsheet and it seems to be testing for "0" (zero). I tried to have it test for "", but that didn't seem to work. There won't be zeros to test for in my scenario. I suppose I can add a column, test for null and have it resolve to zero. Then run your script off that hidden column so that the end user doesn't see it.

Thanks for your help on this. Sometimes it is the little things that I trip up on.

Your rock!

 

[Smallman]

Hi CorrieAnn

For the autofilter procedure null change the'"<>0" to

"<>"

for the other procedure just change this line

If Len(rng.Text) = 0 Then

Should help anyways.

Take care

Smallman

 

[CorrieAnn Gillen]

In my scenario, my data resides in rows A36:A125 so that is the range I used. I then changed the zero to the null as you had instructed. When I run the script it doesn't seem to be doing anything...

Sub Hide()
  [A36:A125].AutoFilter 1, "<>", , , 0
End Sub

I haven't tried out the Unhide portion, for obvious reasons, but this is what I changed it to:

Sub Unhide()
  [A36].AutoFilter
End Sub

 

[Smallman]

I Corrieann

I will post you am example file later tonight australian time showing this technique working on your range. If that does not work, change your range.

Take care

Smallman

 

[Smallman]

Hi CorrieAnn

Sorry for the delay. I am on a boat right now.

Have a look at the file attached. It will hide blank rows. I used exactly the same coding as you have above and it seems to work fine.

If I misunderstood and you need blank and 0 then this code is slightly different again. I have included that in the file too just in case.

Take care

Smallman

 

[CorrieAnn Gillen]

Awesome!!!! This totally works! You rock! Thanks for helping me out.