98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Cells or Tables + deleting unused space
Cells or Tables + deleting unused space
Resolved · Medium Priority · Version 2003
Wed 4 Sep 2013, 16:16Delegate Caspar said...
Caspar has attended:
Excel VBA Intro Intermediate course
Cells or Tables + deleting unused space
Hello again.
I've got a cell (or it could be a table, same idea) which has some text in. After the text there are lots of blank lines.
It would be nice to a have a macro bringing the bottom barrier line of the cell up to the text (whatever that text is) in a tidy way.
I can't find an obvious command for this. Would you happen to know of one?
Or, perhaps another way of asking the same question, why has the deleting that created the empty space in the first place not taken away the blank lines as well?
A way round it might be to say
"If line start within cell is blank, delete that line."
But I imagine this must be a common operation with a recognised approach. Yes? No?
Wed 4 Sep 2013, 19:07Trainer Gary said...
RE: Cells or Tables + deleting unused space
Hi Caspar
Thanks for getting in touch.
I'm guessing what your code does when it finds an empty cell, it clears the contents and doesn't delete the entire row. Hence any formatting - such as a bottom border, I assume that's what you mean by barrier - remains.
When you delete a cell in VBA, you can use the command
ActiveCell.EntireRow.Delete
To remove an entire row. This will pull the content up.
A slightly smarter command would be to automatically put a border on the bottom-most row:
With Range("b2").CurrentRegion.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
You could change "B2" to whatever reference you like.
I hope this helps and gives you some inspiration.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Wed 11 Sep 2013: Automatically marked as resolved.
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Generate randon numbersSome types of analysis require you to use randomly generated numbers. You can also use randomly generated numbers to quickly populate an Excel spreadsheet. There's an easy function you can use to do this automatically. Here are a few of the ways you can use it: |
