Excel hide rows by range 2

[swtrader]

I have a spreadsheet with formulas in 2000 rows. As data is filled (sequentially down the sheet), 'populated' rows are to be printed; 'unpopulated' rows are to be hidden.

I run the following before printing:

Sub HideRows()
Set CheckRange = ActiveSheet.Range ("B2:B2002")
For Each Checkcell in CheckRange
With Checkcell
If .Value = 0 Then
.EntireRow.Hidden = True
End If
End With
Next Checkcell

However, it takes quite a bit of time to check each row and hide it.

It seems like it would be easier to simply hide a range -- without any cell checking necessary.

If I create a "HideRange" range name,

1. How do I change the range parameters to include the newly populated row? (Initially, HideRange would include B2:B50. With newly populated data, the HideRange rows need to be B2:B51.)

2. What is the syntax for hiding a range?

Thanks. You're always most helpful.

swtrader
-- If you don't know where you're going, you'll always know when you're not there.

 

[PHV]

Why not simply an AutoFilter ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Hi,

If you have a contiguous range, then you can define the extremes using the CurrentRegion property. My tables are almost ALL contiguous with A1...

With TheSheetObj.[A1].CurrentRegion
  lRowFst = .Row
  lRowLst = .rows.count + .row - 1
  iColFst = .column
  iColLst = .columns.count + .column - 1
End With

Skip,

_[red][/red]

 

[swtrader]

Hmm - AutoFilter didn't work on first try.

Skip/ this is a bit beyond me.

How do I then Hide the CurrentRegion?

swtrader
-- If you don't know where you're going, you'll always know when you're not there.

 

[PHV]

AutoFilter didn't work
What have you tried ?
DropDown in B1 with criteria: <> 0 ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[swtrader]

Dropdown <> 0; However, my report header rows (A1 thru A8) are Blank. I've tried Custom with 'equals " "' Still hid the header rows. ??

Since I have about 50 sheets running this app, I can't go back and populate the header rows.

swtrader
-- If you don't know where you're going, you'll always know when you're not there.

 

[PHV]

You original post talked about "B2:B2002" and now you talk about A1 thru A8 !

 

[swtrader]

Sorry, B2 thru B8 are the header rows.

swtrader
-- If you don't know where you're going, you'll always know when you're not there.

 

[PHV]

So, the code in your original hides B2:B8 too !

 

[SkipVought]

If you hide the current region, NOTHING would be displayed.

I answered the question...
" How do I change the range parameters to include the newly populated row?"

You hide a range as you would hide a single row.

"B2 thru B8 are the header rows."

In good table design, there is ONE ROW of heading information. The first row is considered heading. All other CONTIGUOUS rows are considered data.

Skip,

_[red][/red]

 

[swtrader]

Great help from both of you. Thanks.

swtrader
-- If you don't know where you're going, you'll always know when you're not there.