Reduce Size of Blank Rows

[Rebecca]

I am using Excel 2007, and I have a column that contains many blank rows.
These blank rows are all 16-point in size, and I want to reduce all of them
to 8-point or even lower in order to tighten up the appearance. Is there any
way I can do this automatically (and without sorting the rows)? Please keep
in mind that I am a very new user, and I am unfamilar with macros and the
like. So if there is a way to do this, please explain the procedure in as
easy English as possible. Thank you.

 

[David McRitchie]

Hi Rebecca,
This solution is with a macro, that you will have to invoke.
If not familiar with macros see
http://www.mvps.org/dmcritchie/excel/getstarted.htm#havemacro

If you want to restart such that all cells are formatted first
as 16 points then include by removing the single quote in front of
' Cells.Font.Size = 16
in the code below.

Sub Reduce_Height_of_Empty_Rows()
'-- Reduce_Height_of_Empty_Rows to 8pts
' D.McRitchie, 2008-02-23 in excel.newusers
Dim LastRow As Long
Dim I As Long
' Cells.Font.Size = 16 â™ 
LastRow = Cells.SpecialCells(xlLastCell).Row
For I = LastRow To 2 Step -1
If Application.CountA(Cells(I, 1).EntireRow) = 0 Then
Cells(I, 1).EntireRow.Font.Size = 8
End If
Next I
End Sub

 

[Rebecca]

Thanks, David. One question before I attempt to create a macro (I am a new
user so I will have to study the info contained in the link you provided.
Wish me luck.). Regarding this line:

' Cells.Font.Size = 16 â™ 

Is the "spade" symbol at the end of the above line correct? Or is it just
to attract my attention?

 

[Gord Dibben]

Rebecca

The symbol after the 16 is a question mark ?

David is giving you the option to change the font size to what you want.

You would also remove the question mark.


Gord Dibben MS Excel MVP

 

[David McRitchie]

sorry, something in Vista or my charset keeps putting
garbage in when I do something like backspace, but
I don't know if it is some legitimate shortcut for special
characters or what. I don't even know what I do to put
it there, it is not on purpose. Remove it, it's not supposed to be
there. Characters not in the font that one is looking
typically show up as ? (question mark) but I do see the
spade myself.

 

[Rebecca]

Hi, David. I created a macro as you instructed, but I keep getting an error
message at the END IF line. Any ideas why this is happening? Though I know
nothing whatsoever about programming, it appears that it will need some
tweaking.

To repeat my problem:

I have certain empty rows that do not contain any data (of course), that are
16-point, but I want to reduce them to 8-point (or maybe 6-point if the rows
still look too big). Could you check once more to see if there is some
problem. Thanks.

 

[David McRitchie]

Should be okay, make sure there are no extraneous characters
on the IF statement and the it ends with Then