I've been rummaging through the posts regarding finding the last row in
a range. They don't quite get me what I need. My ultimate goal is to
created a sub that will hide all rows with the value of "1" in column
A. Without creating a loop for the entire worksheet I want to limit
the loop to a named range, let's say "print_range". To do that I need
to be able to identify the last row of the named range. I don't think
that the Count property will give me the last row if the range doesn't
begin in row 1.

Let's say my named range is from A5:Z999.

Any ideas or insights would be greatly appreciated.

TIA

Hi,
Why not ...

For each cell in Range("Print_Range")
If cell.value = 1 then
cell.entirerow.hidden=true
end if
next cell

"tig" wrote:

I've been rummaging through the posts regarding finding the last row in
a range. They don't quite get me what I need. My ultimate goal is to
created a sub that will hide all rows with the value of "1" in column
A. Without creating a loop for the entire worksheet I want to limit
the loop to a named range, let's say "print_range". To do that I need
to be able to identify the last row of the named range. I don't think
that the Count property will give me the last row if the range doesn't
begin in row 1.

Let's say my named range is from A5:Z999.

Any ideas or insights would be greatly appreciated.

TIA

you can try this method:

Sub hide()
Dim r As Range
For Each r In ActiveSheet.Range("tst").Rows
If r.Cells(1, 1) = "1" Then r.Hidden = True
Next r
Set r = Nothing
End Sub


--
HTHs Martin


"tig" wrote:

I've been rummaging through the posts regarding finding the last row in
a range. They don't quite get me what I need. My ultimate goal is to
created a sub that will hide all rows with the value of "1" in column
A. Without creating a loop for the entire worksheet I want to limit
the loop to a named range, let's say "print_range". To do that I need
to be able to identify the last row of the named range. I don't think
that the Count property will give me the last row if the range doesn't
begin in row 1.

Let's say my named range is from A5:Z999.

Any ideas or insights would be greatly appreciated.

TIA

Hi,
Ignore my last post as your "print_range" is multiple columns UNLESS
you know ONLY column A will contain 1 or "1".

"Toppers" wrote:

Hi,
Why not ...

For each cell in Range("Print_Range")
If cell.value = 1 then
cell.entirerow.hidden=true
end if
next cell

"tig" wrote:

I've been rummaging through the posts regarding finding the last row in
a range. They don't quite get me what I need. My ultimate goal is to
created a sub that will hide all rows with the value of "1" in column
A. Without creating a loop for the entire worksheet I want to limit
the loop to a named range, let's say "print_range". To do that I need
to be able to identify the last row of the named range. I don't think
that the Count property will give me the last row if the range doesn't
begin in row 1.

Let's say my named range is from A5:Z999.

Any ideas or insights would be greatly appreciated.

TIA

Martin,

Thank you for the reply. The method you gave me works pretty well. So
I'm a little better off than I was earlier. The only problem is that
the range is usually over 1000 rows. So it takes quite a while to go
through the loop.

Any ideas on speeding it up?? One thing I thought of was to create a
named range with the rows with column A = 1, then I could just do
Range("test").Rows.Hidden = True.

My problem is I'm not sure if I can programmatically populate a named
range in a loop like that. And who knows, it might not even be much
faster.

Let me know if you have any further insights.

Thanks again.

Why Not Filter the column

Sub Hide_with_Autofilter()
Dim HideValue As String
Dim rng As Range

HideValue = "ron"
' This will hide the rows with "ron" in the Range("A1:A100")
With ActiveSheet
.Range("A1:A100").AutoFilter Field:=1, Criteria1:=HideValue
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0

End With
.AutoFilterMode = False
End With

If Not rng Is Nothing Then rng.EntireRow.Hidden = True

End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"tig" wrote in message oups.com...
Martin,

Thank you for the reply. The method you gave me works pretty well. So
I'm a little better off than I was earlier. The only problem is that
the range is usually over 1000 rows. So it takes quite a while to go
through the loop.

Any ideas on speeding it up?? One thing I thought of was to create a
named range with the rows with column A = 1, then I could just do
Range("test").Rows.Hidden = True.

My problem is I'm not sure if I can programmatically populate a named
range in a loop like that. And who knows, it might not even be much
faster.

Let me know if you have any further insights.

Thanks again.

There is one tweek that you can play around with in VBA/excel:

at the start of the sub include:

Application.ScreenUpdating = false


and at the end of the sub include (if you are in 2002+ then it is very
important to include this):

Application.ScreenUpdating = true

Excel before 2002 automatically turned screenupdating back on at the end of
macro now it does not and if you halt your code or you get exceptions you
need to write a little macro that will turn it back on and keep it in your
personal:

sub TurnScreenUpdatingOn()
Application.ScreenUpdating = true
end sub

The other area is in code optimisation. Display your code I will review it
or someone else will if I've gone to bed. LOL 21:30 here.LOL

--
HTHs Martin


"tig" wrote:

Martin,

Thank you for the reply. The method you gave me works pretty well. So
I'm a little better off than I was earlier. The only problem is that
the range is usually over 1000 rows. So it takes quite a while to go
through the loop.

Any ideas on speeding it up?? One thing I thought of was to create a
named range with the rows with column A = 1, then I could just do
Range("test").Rows.Hidden = True.

My problem is I'm not sure if I can programmatically populate a named
range in a loop like that. And who knows, it might not even be much
faster.

Let me know if you have any further insights.

Thanks again.

Martin,

I tried the ScreenUpdatingOn you suggested. That made it a little
faster. I'll attach my code in case you can't sleep. :-) or someone
else wants a crack at it.

In the mean time I'm going to try to incorporate Ron's Filter idea.

Thank you both for your help so far.

Sub ad_hide()

Application.ScreenUpdating = False

Dim r As Range
For Each r In ActiveSheet.Range("Print_area").Rows
If IsNumeric(r.Cells(1, 1 - 1)) Then
If r.Cells(1, 1 - 1) = 1 Then
r.Hidden = False
Else
r.Hidden = True
End If
Else
r.Hidden = True
End If
Next r
Set r = Nothing

Application.ScreenUpdating = True

End Sub

Thanks for the reply Martin. I working in Excel 97 right now. Based
on your notes, it may not make a difference. Right?

My code is at the top of the post.

Thanks again.

Sorry Martin. I replied to the wrong post. Duh!!