Hide many rows quick without loop

[Imbecill]

1/ Anybody recommend a good way of hiding many rows quick without the delay
a loop row by row mean? I guess Worksheet_activate is the best trigger.

All rows with "cell in column 2 = empty" should be hided, between 50 - 200
rows in each sheet

2/ An extra twist: - There is more than 10 sheets this should occur for - is
there something I can put in the workbook module?

/Thanks.

 

[Bernie Deitrick]

Imbecill,

In the codemodule of the Thisworkbook object, paste this code:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.ScreenUpdating = False
On Error Resume Next
Sh.Cells.EntireRow.Hidden = False
Sh.Range("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
Application.ScreenUpdating = True
End Sub

This assumes empty means empty, not blank from a formula returning "".

HTH,
Bernie
MS Excel MVP

 

[Imbecill]

Thank you,

I realised I also have formulas in B:B delvering "". I'm not familiare with
expression xlCellTypeBlanks, kan you modify it for me?

/Many thanks

 

[Bernie Deitrick]

Imbecill,

I would need to know if your formulas usually return numbers or string, or
what they actually can return. If they typically return numbers except when
they return "" then you could use:

SpecialCells(xlCellTypeFormulas, 2)

If they always return strings, then you modify your formulas to return
numbers instead of the "", in which case you could use:

SpecialCells(xlCellTypeFormulas, 1)

If they can return either normally, then you could them to return errors
istead of the "" (simply repleace the "" part with 1/0) and you could use:

..SpecialCells(xlCellTypeFormulas, 16)

Each of these would be in place of the xlCellTypeBlanks.

HTH,
Bernie
MS Excel MVP

 

[Imbecill]

Great thank you Bernie

They are always stings so it wil work out well, I guess.

/Best Regards

 

[Jay]

1/ Anybody recommend a good way of hiding many rows quick without the
delay a loop row by row mean? I guess Worksheet_activate is the best
trigger.

All rows with "cell in column 2 = empty" should be hided, between 50
- 200 rows in each sheet

One way is to use
Data >> Filter >> AutoFilter

If you auto-filter on column 2, you can use the pill-down list and choose
"non-blanks" to display.

In one case, I have a "helper" column to use auto-filter on. In some rows,
it uses a formula to return either a blank or a non-blank. For example, I
want a row to be hidden if all its cells in a certain range of columns are
empty. The helper column can itself be hidden to make things look tidy.

 

[Imbecill]

Bernie, can you or any else modify this " Sub Workbook_SheetActivate " to
the event for a specific workbook using Sub Worksheet_Activate instead?

Thank you for your patience beyond limits...

/Imbecill

 

[Dave Peterson]

Place this under the worksheet module:

Option Explicit
Private Sub Worksheet_Activate()

Application.ScreenUpdating = False
On Error Resume Next
Me.Cells.EntireRow.Hidden = False
Me.Range("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
Application.ScreenUpdating = True

End Sub


But if you wanted the same functionality for a few worksheets in the same
workbook, you could modify Bernie's original code to check the sheet name first
(still under ThisWorkbook):

Option Explicit
Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Select Case LCase(Sh.Name)
Case Is = "sheet1", "sheet2"
Application.ScreenUpdating = False
On Error Resume Next
Sh.Cells.EntireRow.Hidden = False
Sh.Range("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden =
True
Application.ScreenUpdating = True
Case Else
'do nothing
End Select

End Sub

 

[Imbecill]

Thank you all!

Dave, I've learned something new today, the "Me." As a last input maybe you
or sombody else can give me a clue how to reduce the B:B range to say 1000
rows?

I've picked out this as best suitable for the current needs:

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
On Error Resume Next
Me.Cells.EntireRow.Hidden = False
Me.Range("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden
= True
Application.ScreenUpdating = True
End Sub

The code does still take a while, not as much as with a loop row by row, but
unnessesarily long.

Since I 'm new with " Me. ", I 've not find out how to achieve this -
checking the first 1000 rows, I mean, to speed up the process.

/Regards,
"Asking once more in shame - soon you've written all my code...."

Place this under the worksheet module:

Option Explicit
Private Sub Worksheet_Activate()

Application.ScreenUpdating = False
On Error Resume Next
Me.Cells.EntireRow.Hidden = False
Me.Range("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
Application.ScreenUpdating = True

End Sub


But if you wanted the same functionality for a few worksheets in the same
workbook, you could modify Bernie's original code to check the sheet name first
(still under ThisWorkbook):

Option Explicit
Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Select Case LCase(Sh.Name)
Case Is = "sheet1", "sheet2"
Application.ScreenUpdating = False
On Error Resume Next
Sh.Cells.EntireRow.Hidden = False

Sh.Range("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden =

 

[Bernie Deitrick]

Imbecill,

Simply replace the "B:B" with "B1:B1000"

HTH,
Bernie
MS Excel MVP

Thank you all!

Dave, I've learned something new today, the "Me." As a last input maybe you
or sombody else can give me a clue how to reduce the B:B range to say 1000
rows?

I've picked out this as best suitable for the current needs:

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
On Error Resume Next
Me.Cells.EntireRow.Hidden = False
Me.Range("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden
= True
Application.ScreenUpdating = True
End Sub

The code does still take a while, not as much as with a loop row by row, but
unnessesarily long.

Since I 'm new with " Me. ", I 've not find out how to achieve this -
checking the first 1000 rows, I mean, to speed up the process.

/Regards,
"Asking once more in shame - soon you've written all my code...."

Place this under the worksheet module:

Option Explicit
Private Sub Worksheet_Activate()

Application.ScreenUpdating = False
On Error Resume Next
Me.Cells.EntireRow.Hidden = False
Me.Range("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
Application.ScreenUpdating = True

End Sub


But if you wanted the same functionality for a few worksheets in the same
workbook, you could modify Bernie's original code to check the sheet

name

first
(still under ThisWorkbook):

Option Explicit
Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Select Case LCase(Sh.Name)
Case Is = "sheet1", "sheet2"
Application.ScreenUpdating = False
On Error Resume Next
Sh.Cells.EntireRow.Hidden = False

Sh.Range("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden =

True
Application.ScreenUpdating = True
Case Else
'do nothing
End Select

End Sub

between
50 -

 

[Dave Peterson]

Change:
Me.Range("B:B").....
to
Me.Range("B1:B1000")....

but I'm guessing that when you hit ctrl-end, it takes you way past what you
think it should be.

If that's true, then take a look at Debra Dalgleish's site for some techniques
for resetting that used cell:
http://www.contextures.com/xlfaqApp.html#Unused

 

[Imbecill]

You are right, Dave. That was why I had to ask. I've tried with "B1:B1000"
änd it ended up far, far further than 1000...so I thougt I'd missed
something. I'll check the suggested site!

/Thank you, Dave and Bernie.

 

[Imbecill]

Dave, you was right about last cell were far down - in some sheets the last
cell is in the very last row of the sheet!

I've deleted ALL rows under my "real last used row" but still, the scrollbar
go to the very last row. Any suggestions? I there is a littel format box
checked that I don't notice - can I reset formats somehow?

Maybe it is some macros doing this. I have some numbers om Sub to check, any
suggestions what action could cause it? I have no format coditions on these
cells, anyhow.

Could it be caused by then I locked all cells on the cheet and then just
unlocked a few allowed to change.

Suggestions?

 

[Dave Peterson]

It could have been a macro, but when my usedrange gets blown past where I
expected it, it's usually because I removed rows (or added some stuff that I
deleted later).

Deb's site has a bunch of instructions when you do this manually.

One of them is that for some versions of excel, you need to save the workbook.
(And I think with some early, early versions, you had to save, close and reopen
the workbook.)

What version of excel are you using?

(And you actually deleted the rows (and columns)--not just cleared contents,
right???)

Dave, you was right about last cell were far down - in some sheets the last
cell is in the very last row of the sheet!

I've deleted ALL rows under my "real last used row" but still, the scrollbar
go to the very last row. Any suggestions? I there is a littel format box
checked that I don't notice - can I reset formats somehow?

Maybe it is some macros doing this. I have some numbers om Sub to check, any
suggestions what action could cause it? I have no format coditions on these
cells, anyhow.

Could it be caused by then I locked all cells on the cheet and then just
unlocked a few allowed to change.

Suggestions?

 

[Imbecill]

Yes I,ve been at Deb's site and evalutated that and I.ve selected every row
below my dataArea and _took_away_the_rows. I've done the same with the
columns to the right in hope it was something in oneof the unused columns
causing it, but it wasn't.

I'm running Excel 2002 Sp3.

I guess I had to search in the code for a macro doing something with the
cells I cant visually see. Anyone having suggestions what kind of formats
etc. that make excel believe I use everey row in the sheet?


/Regards

 

[Dave Peterson]

Try cleaning up the usedrange once more.
Immediately after you do that, hit ctrl-end.
Does the cursor go to where you expected it to go or is it still way off?

If it's still way off, then either you didn't accomplish what you tried or a
macro ran really quickly.

If you still think it's a macro, try:
close excel
windows start button|Run
excel /safe
(to disable all macros)

Try resetting that last used cell and test it out once more.

If it worked after you did the same stuff, I'd start looking for a macro.


Else I'd print Deb's instructions (making it a checklist) and try it once more.

Yes I,ve been at Deb's site and evalutated that and I.ve selected every row
below my dataArea and _took_away_the_rows. I've done the same with the
columns to the right in hope it was something in oneof the unused columns
causing it, but it wasn't.

I'm running Excel 2002 Sp3.

I guess I had to search in the code for a macro doing something with the
cells I cant visually see. Anyone having suggestions what kind of formats
etc. that make excel believe I use everey row in the sheet?

/Regards