Having a floating row or hiding unused rows?

[Simon Lloyd]

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

 

[Guest]

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 Data>List>Create 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.

 

[Johnny]

Only works in Excel 2003

 

[Simon Lloyd]

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

 

[Guest]

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

 

[Simon Lloyd]

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