Having a floating row or hiding unused rows?
 

Hi all, I have an accounts sheet where row 1000 has all the sum totals,
is there anyway of getting this to be a floating row? i.e showing under
the last row used, or can i get all rows with no data in them between
the last used row (that contains data as the cells are already
formatted...borders etc.) and row 1000 to hide i can use an autoshape
or Cmd button for that, just need to know how to do it!

Hope you can help,

Regards,
Simon


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=551964

Having a floating row or hiding unused rows?
 

Simon, you could set your data up as a data list which would not require any
code to get you want you want. First, highlight all the data in your sheet.
Then, click DataListCreate list. Indicate if your highlighted list
includes a header. Once the list is created, you can toggle a totals row on
or off from the data list toolbar (which will appear whenever you click in
the list). The list will automatically expand as you add new data items.


"Simon Lloyd" wrote:


Hi all, I have an accounts sheet where row 1000 has all the sum totals,
is there anyway of getting this to be a floating row? i.e showing under
the last row used, or can i get all rows with no data in them between
the last used row (that contains data as the cells are already
formatted...borders etc.) and row 1000 to hide i can use an autoshape
or Cmd button for that, just need to know how to do it!

Hope you can help,

Regards,
Simon


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=551964

Having a floating row or hiding unused rows?
 

Only works in Excel 2003

Having a floating row or hiding unused rows?
 

Thanks for the reply but it doesn't quite manage what i need, at the end
of each row is a formula for a sub total subtracting one cell from
another then adding the amount from a previous cell, the way the list
creation works does not allow you to modify formulas.

Any other ideas?

regards,

Simon


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=551964

Having a floating row or hiding unused rows?
 

Assuming there will be data in column A for the last used row prior to 1000
and your data would never go all the way to row 999:

Rows.Hidden = false
set rng = cells(999,1).End(xlup)(2)
range(rng,cells(999,1)).Entirerow.Hidden = True

--
Regards,
Tom Ogilvy

"Simon Lloyd" wrote:


Hi all, I have an accounts sheet where row 1000 has all the sum totals,
is there anyway of getting this to be a floating row? i.e showing under
the last row used, or can i get all rows with no data in them between
the last used row (that contains data as the cells are already
formatted...borders etc.) and row 1000 to hide i can use an autoshape
or Cmd button for that, just need to know how to do it!

Hope you can help,

Regards,
Simon


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=551964

Having a floating row or hiding unused rows?
 

Tom, thanks for the reply, did what i needed, there won't always be dat
in column A but there will be in T so i changed it.

Great!

Simo

--
Simon Lloy
-----------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...nfo&userid=670
View this thread: http://www.excelforum.com/showthread.php?threadid=55196