<Jake.Marx@americawest.com>De: Jake Marx 
Objet: Re: Hiding rows without screen refresh ?
Date : vendredi 15 octobre 1999 00:33

Sunil,

Here's an example of how you can do the hide all at once (instead of one row
at a time).  if you want it to be case-insensitive, put the line Option
Compare Text at the top of your module.  You can change the constants in the
subroutine to adapt it to your situation.  Alternatively, you could use the
selection range object to get the first and last row of the
currently-selected range.  That way you could run the routine on the range
the user selects.  This should speed up your code quite a bit.

Sub HideifNottotal()
    dim rngChk As range
    dim rngHide As range
    dim lCurrRow As long

    Const lSTART_ROW As long = 1
    Const lend_ROW As long = 20
    Const nCOL_TO_CHK As Integer = 1

    application.screenupdating = false

    with Sheet1
        .range("A" & lSTART_ROW & ":A" & lend_ROW).EntireRow.Hidden _
         = false
        for lCurrRow = lSTART_ROW to lend_ROW
            set rngChk = .cells(lCurrRow, nCOL_TO_CHK)
            if rngChk.value <> "TOTAL"<sunil@sunil.freeserve.co.uk> then
                '/ add to range to hide
                if Not rngHide Is Nothing then
                    set rngHide = Union(rngHide, rngChk)
                Else
                    set rngHide = rngChk
                end if
            end if
        next lCurrRow
    end with

    if Not rngHide Is Nothing then
        rngHide.EntireRow.Hidden = true
    end if

    application.screenupdating = true
    set rngChk = Nothing
    set rngHide = Nothing
end Sub


Regards,
Jake Marx


Sunil Patel  wrote in message
news:7u5hs2$1po$1@news7.svr.pol.co.uk...
> The following macro works but as i have approx 100 rows which hide
> the screen refresh between each row takes too long on my old machine.
> Can anyone tell me if it if possible to hide non continous row numbers all
> at once
> if the first cell in the row is NOT "TOTAL".
> Thanks.
>
> Sub hiderow()
> dim R
>     for R = 1 to 20
>
>  activecell.EntireRow.Hidden = false 'UNhide the row
>         if activecell.value <> "TOTAL" then
>            activecell.EntireRow.Hidden = true 'hide the row
>            end if
>     activecell.Offset(1, 0).select  'go to next cell in range
>     next R
>        activesheet.range("a1").select 'go to top of sheet
>
> end Sub
>
>
>
>
>
>
>