hiding most of the rows in a sheet

[cheveley]

I am trying to hide most of the rows in a sheet but when I attempt to do so I get the following errror message "cannot shift object off sheet".

Can anyone give me a pointer to correct this.

Thanks.

 

[nbrcrunch]

you have either a chart or a drawing object on the sheet.

 

[erik.van.geit]

Hi, cheveley,

don't know about details but you have some shapes on your sheet which "need" enough visible rows to be displayed

what you can do is expand the last visible row (or more rows) to the maximum height and color them like your excelbackground

kind regards,
Erik

 

[cheveley]

thanks guys for the solution(s)

problem solved

 

[erik.van.geit]

cheveley,
for future reference
can you tell what was the problem and how you solved it please?

 

[cheveley]

oh yes, how does one find a drawing object on a worksheet if you dont know where it is?

thanks again.

 

[erik.van.geit]

Sub test()
For Each sh In ActiveSheet.Shapes
'sh.Select
sh.TopLeftCell.Select
Next sh
End Sub

step through code with function key F8 or use a "stop" line or a breakpoint
or Application.Wait Now + 2 / 60 / 60 / 24 (wait 2 seconds)

 

[cheveley]

hi erik

thought i had solved the problem by deleting a named cell but that does not seem to have done the trick (worked first time). funny thing is the worksheet does not have any charts and I dont think there are any drawing objects (unless by accident).

still playing, will post when have solution............

 

[erik.van.geit]

updated the code to be sure you would understand

Sub test()
For Each sh In ActiveSheet.Shapes
sh.TopLeftCell.Select
Application.Wait Now + 2 / 60 / 60 / 24
Next sh
End Sub

this will show you where they are

 

[cheveley]

thanks for the code but I would not even be sure where to stick it, in module 1 in VB?