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?
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
