Seeking a solution to a time-consuming problem...

  • Thread starter Thread starter davidd31415
  • Start date Start date
D

davidd31415

I am hoping someone can recommend a solution for a time-consuming
problem that I run into often.

I create and modify Excel sheets that are used to record data on a
regular basis. Often the modifications done to these sheets require
adding or deleting rows and cells in the middle of the sheet (the upper
and lower portions are headers with fields and borders, and con not
simply be entered in as headers/footers. Herein lies my problem: when
adding, removing, and resizing columns in the middle of the sheet the
upper and lower portions of the sheet become distorted. I usually end
up un-merging, clearing all borders, moving text to the left or right,
re-merging, and wasting a lot of time making the header span across the
page similar to how it did in the first place.

I have tried some custom VBA code to produce these sheets as well as
just typing in the upper and lower portions of the sheets as well, but
each ends up having its disadvantages. The VBA programs I have written
are not quite flexible enough to accommodate all the different sheets
that I need to generate (many are one-timers or are unique) and typing
everything in is mundane and time consuming. I have been considering
writing a VBA program that will simply take a sheet and add the upper
and lower portions in, but before I do so I would like to see if I can
find anyone else who has dealt with similar problems and see how they
solved them.

If anyone has recommendations on how to deal with this issue, I would
appreciate reading them!

Thanks,

David.
 
my reccomendation is to throw Excel out the Window and use Access for
reporting like this.

-Aaron
 
Before you throw excel out of the window ( :>))

Have you tried to create one or a few text boxes of a standard size and
after deletion/ addition of columns......... resize the width of the
columns?
You could have hidden empty columns which could be unhidden and resized.
You can always send me an example with an indication of a worst scenario.
 
I'm not quite sure what you mean by using the text boxes with resizing
columns... The column sizes usually depend more on the middle part of
the sheet. Hiding columns may be a great time saver, I'm going to give
that a try. Writing a macro to resize & unhide specific columns at
specific sizes would be a cinch and if it didn't affect the other
portions of the sheet, that'd be great. As long as I keep the ends of
the columns that are merged unhidden it looks like things will work out
nice (I guess I never realized that a column in the middle of a merge
COULD be hidden before)!

Thanks for the idea, I'll let you know how it works out.

Dave

(Re: throwing out Excel, if it were up to me, I'd go for it, but
there's a long uphill political battle before I'll get to a db
solution, unfortunately).
 
It's just that text boxes are not linked to certain cells or columns.
You could have more than one per "header" or "footer"
Also a textbox does not have to have a border.

Do you really have to merge cells?
 
Back
Top