Reduce Size of Blank Rows

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rebecca - 23 Feb 2008 15:45 GMT

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.

Reply to this Message

David McRitchie - 23 Feb 2008 19:33 GMT

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

Signature

HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm

>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
[quoted text clipped - 3 lines]
> like. So if there is a way to do this, please explain the procedure in as
> easy English as possible. Thank you.

Reply to this Message

Rebecca - 24 Feb 2008 01:13 GMT

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?

> Hi Rebecca,
> This solution is with a macro, that you will have to invoke.
[quoted text clipped - 27 lines]
> > like. So if there is a way to do this, please explain the procedure in as
> > easy English as possible. Thank you.

Reply to this Message

Gord Dibben - 24 Feb 2008 02:04 GMT

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.

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

You would also remove the question mark.

Gord Dibben MS Excel MVP

>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.
[quoted text clipped - 36 lines]
>> > like. So if there is a way to do this, please explain the procedure in as
>> > easy English as possible. Thank you.

Reply to this Message

David McRitchie - 24 Feb 2008 02:17 GMT

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.

Signature

HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm

> Rebecca
>
[quoted text clipped - 50 lines]
>>> > like. So if there is a way to do this, please explain the procedure in as
>>> > easy English as possible. Thank you.

Reply to this Message

Rebecca - 25 Feb 2008 10:07 GMT

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.

> sorry, something in Vista or my charset keeps putting
> garbage in when I do something like backspace, but
[quoted text clipped - 59 lines]
> >>> > like. So if there is a way to do this, please explain the procedure in as
> >>> > easy English as possible. Thank you.

Reply to this Message

David McRitchie - 25 Feb 2008 22:25 GMT

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

Signature

HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm

> 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
[quoted text clipped - 71 lines]
>> >>> > like. So if there is a way to do this, please explain the procedure in as
>> >>> > easy English as possible. Thank you.

Reply to this Message