Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Reduce Size of Blank Rows (again)
I am using MS Excel 2007. A month or so ago I asked for help to reduce the
size of blank rows (currently 16 points) down to 8 points. The Excel experts advised me to try this macro: 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 Whenever I run this macro, it doesn't do the job intended (it's hard to explain -- because I know nothing about programming -- but it looks like the macro is running in loops -- the rows appear to be stuttering, but not reducing the size from 16 to 8), and when I press Escape, the debug is yellow highlight at the "End IF" line. As I mentioned above I know absolutely nothing about macro creation, so I am at a loss as to what the problem is. Could one of the MVP Excel experts take a quick look again at the macro above and give me some advice? Thank you. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Reduce Size of Blank Rows (again)
Your macro is changing the font for the cells in the empty rows.
That means that you really won't see anything until you type something in one of those cells. If you really wanted to change the rowheight of those empty rows to 8, you could use: Option Explicit Sub Reduce_Height_of_Empty_Rows2() Dim LastRow As Long Dim I As Long LastRow = Cells.SpecialCells(xlLastCell).Row For I = LastRow To 2 Step -1 If Application.CountA(Rows(I)) = 0 Then Rows(I).RowHeight = 8 End If Next I End Sub Rebecca wrote: I am using MS Excel 2007. A month or so ago I asked for help to reduce the size of blank rows (currently 16 points) down to 8 points. The Excel experts advised me to try this macro: 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 Whenever I run this macro, it doesn't do the job intended (it's hard to explain -- because I know nothing about programming -- but it looks like the macro is running in loops -- the rows appear to be stuttering, but not reducing the size from 16 to 8), and when I press Escape, the debug is yellow highlight at the "End IF" line. As I mentioned above I know absolutely nothing about macro creation, so I am at a loss as to what the problem is. Could one of the MVP Excel experts take a quick look again at the macro above and give me some advice? Thank you. -- Dave Peterson |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Reduce Size of Blank Rows (again)
Thanks, Dave, but it still doesn't work. It still stops at the End If. Do you
need more information about the worksheet (for example, it contains a mixture of unicode fonts and non-unicode fonts). Is that what's causing the problem? As you can see, I am a new user and I'm unable to program a macro. "Dave Peterson" wrote: Your macro is changing the font for the cells in the empty rows. That means that you really won't see anything until you type something in one of those cells. If you really wanted to change the rowheight of those empty rows to 8, you could use: Option Explicit Sub Reduce_Height_of_Empty_Rows2() Dim LastRow As Long Dim I As Long LastRow = Cells.SpecialCells(xlLastCell).Row For I = LastRow To 2 Step -1 If Application.CountA(Rows(I)) = 0 Then Rows(I).RowHeight = 8 End If Next I End Sub Rebecca wrote: I am using MS Excel 2007. A month or so ago I asked for help to reduce the size of blank rows (currently 16 points) down to 8 points. The Excel experts advised me to try this macro: 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 Whenever I run this macro, it doesn't do the job intended (it's hard to explain -- because I know nothing about programming -- but it looks like the macro is running in loops -- the rows appear to be stuttering, but not reducing the size from 16 to 8), and when I press Escape, the debug is yellow highlight at the "End IF" line. As I mentioned above I know absolutely nothing about macro creation, so I am at a loss as to what the problem is. Could one of the MVP Excel experts take a quick look again at the macro above and give me some advice? Thank you. -- Dave Peterson |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Reduce Size of Blank Rows (again)
First, what are you trying to do?
Are you changing the font size (for the characters) or the rowheight? Second, I don't understand what "It still stops at the End If" statement means. Maybe you could explain what you want and what is happening in a little more detail. Rebecca wrote: Thanks, Dave, but it still doesn't work. It still stops at the End If. Do you need more information about the worksheet (for example, it contains a mixture of unicode fonts and non-unicode fonts). Is that what's causing the problem? As you can see, I am a new user and I'm unable to program a macro. "Dave Peterson" wrote: Your macro is changing the font for the cells in the empty rows. That means that you really won't see anything until you type something in one of those cells. If you really wanted to change the rowheight of those empty rows to 8, you could use: Option Explicit Sub Reduce_Height_of_Empty_Rows2() Dim LastRow As Long Dim I As Long LastRow = Cells.SpecialCells(xlLastCell).Row For I = LastRow To 2 Step -1 If Application.CountA(Rows(I)) = 0 Then Rows(I).RowHeight = 8 End If Next I End Sub Rebecca wrote: I am using MS Excel 2007. A month or so ago I asked for help to reduce the size of blank rows (currently 16 points) down to 8 points. The Excel experts advised me to try this macro: 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 Whenever I run this macro, it doesn't do the job intended (it's hard to explain -- because I know nothing about programming -- but it looks like the macro is running in loops -- the rows appear to be stuttering, but not reducing the size from 16 to 8), and when I press Escape, the debug is yellow highlight at the "End IF" line. As I mentioned above I know absolutely nothing about macro creation, so I am at a loss as to what the problem is. Could one of the MVP Excel experts take a quick look again at the macro above and give me some advice? Thank you. -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Reduce Size of Blank Rows (again)
Dave, I apologize for not being more specific. I am using MS Excel 2007. I
have a column in a worksheet that contains rows (of course), some of which contain text (a mixture of unicode and non-unicode), some of which are blank (empty / no text). The entire column contains material that was pasted in from external data, and everything is in entire column 16-point font (the heights of the rows varies according to the content of the cells. I want to reduce the size (height) of the empty / blank rows in the entire column to 8-points, that is half the current height. Now when if I select the current 16-point row and select 8-point font, the height of the row is thereby reduced (of course). Apparently (I could be wrong) the macro you provided is aimed at reducing the font size of the contents of the rows, not the height, but shouldn't the height reduction happen anyway? To repeat: I want to reduce the size /height of the rows, from the current 16-point (the entire column) to 8-point (half the size). This applies only to those rows that are empty of text. When I run the macro you created, the worksheet column flickers / blinks and appear to be louping (it acts like a macro that is struggling -- how is this for a newbie explanation?). When I press ESCAPE, the debug window show a yellow highlight at the "End If" line of the macro (and nothing has been changed in the column). I could do a Camtasia so you could see what's happening, if you think my explanation above it inadequate. Again, thanks for your help. "Dave Peterson" wrote: First, what are you trying to do? Are you changing the font size (for the characters) or the rowheight? Second, I don't understand what "It still stops at the End If" statement means. Maybe you could explain what you want and what is happening in a little more detail. Rebecca wrote: Thanks, Dave, but it still doesn't work. It still stops at the End If. Do you need more information about the worksheet (for example, it contains a mixture of unicode fonts and non-unicode fonts). Is that what's causing the problem? As you can see, I am a new user and I'm unable to program a macro. "Dave Peterson" wrote: Your macro is changing the font for the cells in the empty rows. That means that you really won't see anything until you type something in one of those cells. If you really wanted to change the rowheight of those empty rows to 8, you could use: Option Explicit Sub Reduce_Height_of_Empty_Rows2() Dim LastRow As Long Dim I As Long LastRow = Cells.SpecialCells(xlLastCell).Row For I = LastRow To 2 Step -1 If Application.CountA(Rows(I)) = 0 Then Rows(I).RowHeight = 8 End If Next I End Sub Rebecca wrote: I am using MS Excel 2007. A month or so ago I asked for help to reduce the size of blank rows (currently 16 points) down to 8 points. The Excel experts advised me to try this macro: 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 Whenever I run this macro, it doesn't do the job intended (it's hard to explain -- because I know nothing about programming -- but it looks like the macro is running in loops -- the rows appear to be stuttering, but not reducing the size from 16 to 8), and when I press Escape, the debug is yellow highlight at the "End IF" line. As I mentioned above I know absolutely nothing about macro creation, so I am at a loss as to what the problem is. Could one of the MVP Excel experts take a quick look again at the macro above and give me some advice? Thank you. -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Reduce Size of Blank Rows (again)
Just to add one more thing for clarification: all the text in the column are
16-points, though the heights of the rows containing text are different, of course. All the blank / empty rows are 16-points in height. "Rebecca" wrote: Dave, I apologize for not being more specific. I am using MS Excel 2007. I have a column in a worksheet that contains rows (of course), some of which contain text (a mixture of unicode and non-unicode), some of which are blank (empty / no text). The entire column contains material that was pasted in from external data, and everything is in entire column 16-point font (the heights of the rows varies according to the content of the cells. I want to reduce the size (height) of the empty / blank rows in the entire column to 8-points, that is half the current height. Now when if I select the current 16-point row and select 8-point font, the height of the row is thereby reduced (of course). Apparently (I could be wrong) the macro you provided is aimed at reducing the font size of the contents of the rows, not the height, but shouldn't the height reduction happen anyway? To repeat: I want to reduce the size /height of the rows, from the current 16-point (the entire column) to 8-point (half the size). This applies only to those rows that are empty of text. When I run the macro you created, the worksheet column flickers / blinks and appear to be louping (it acts like a macro that is struggling -- how is this for a newbie explanation?). When I press ESCAPE, the debug window show a yellow highlight at the "End If" line of the macro (and nothing has been changed in the column). I could do a Camtasia so you could see what's happening, if you think my explanation above it inadequate. Again, thanks for your help. "Dave Peterson" wrote: First, what are you trying to do? Are you changing the font size (for the characters) or the rowheight? Second, I don't understand what "It still stops at the End If" statement means. Maybe you could explain what you want and what is happening in a little more detail. Rebecca wrote: Thanks, Dave, but it still doesn't work. It still stops at the End If. Do you need more information about the worksheet (for example, it contains a mixture of unicode fonts and non-unicode fonts). Is that what's causing the problem? As you can see, I am a new user and I'm unable to program a macro. "Dave Peterson" wrote: Your macro is changing the font for the cells in the empty rows. That means that you really won't see anything until you type something in one of those cells. If you really wanted to change the rowheight of those empty rows to 8, you could use: Option Explicit Sub Reduce_Height_of_Empty_Rows2() Dim LastRow As Long Dim I As Long LastRow = Cells.SpecialCells(xlLastCell).Row For I = LastRow To 2 Step -1 If Application.CountA(Rows(I)) = 0 Then Rows(I).RowHeight = 8 End If Next I End Sub Rebecca wrote: I am using MS Excel 2007. A month or so ago I asked for help to reduce the size of blank rows (currently 16 points) down to 8 points. The Excel experts advised me to try this macro: 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 Whenever I run this macro, it doesn't do the job intended (it's hard to explain -- because I know nothing about programming -- but it looks like the macro is running in loops -- the rows appear to be stuttering, but not reducing the size from 16 to 8), and when I press Escape, the debug is yellow highlight at the "End IF" line. As I mentioned above I know absolutely nothing about macro creation, so I am at a loss as to what the problem is. Could one of the MVP Excel experts take a quick look again at the macro above and give me some advice? Thank you. -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Reduce Size of Blank Rows (again)
Actually, the macro you got from David McRitchie changed the font size. (The
macro that I suggested changed the rowheight--not what you really wanted, so discard it.) Changing the fontsize of those empty rows should change the rowheight, too--but if you've manually adjusted the rowheight of any row, then that rowheight won't be changed. You could autofit the rows--either all at once or one at a time. All at once will affect the all the rows--even the non-empty rows. Option Explicit Sub Reduce_Height_of_Empty_Rows2() '-- 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(Rows(I)) = 0 Then Rows(I).Font.Size = 8 End If Next I ActiveSheet.Rows.AutoFit End Sub If you don't want to change the height of the rows with data, you could use: Option Explicit Sub Reduce_Height_of_Empty_Rows3() '-- 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(Rows(I)) = 0 Then Rows(I).Font.Size = 8 Rows(I).autofit End If Next I End Sub I changed David McRitchies code in a couple of spots. cells(I,1).entirerow.... can be rewritten as: rows(i)... Rebecca wrote: Dave, I apologize for not being more specific. I am using MS Excel 2007. I have a column in a worksheet that contains rows (of course), some of which contain text (a mixture of unicode and non-unicode), some of which are blank (empty / no text). The entire column contains material that was pasted in from external data, and everything is in entire column 16-point font (the heights of the rows varies according to the content of the cells. I want to reduce the size (height) of the empty / blank rows in the entire column to 8-points, that is half the current height. Now when if I select the current 16-point row and select 8-point font, the height of the row is thereby reduced (of course). Apparently (I could be wrong) the macro you provided is aimed at reducing the font size of the contents of the rows, not the height, but shouldn't the height reduction happen anyway? To repeat: I want to reduce the size /height of the rows, from the current 16-point (the entire column) to 8-point (half the size). This applies only to those rows that are empty of text. When I run the macro you created, the worksheet column flickers / blinks and appear to be louping (it acts like a macro that is struggling -- how is this for a newbie explanation?). When I press ESCAPE, the debug window show a yellow highlight at the "End If" line of the macro (and nothing has been changed in the column). I could do a Camtasia so you could see what's happening, if you think my explanation above it inadequate. Again, thanks for your help. "Dave Peterson" wrote: First, what are you trying to do? Are you changing the font size (for the characters) or the rowheight? Second, I don't understand what "It still stops at the End If" statement means. Maybe you could explain what you want and what is happening in a little more detail. Rebecca wrote: Thanks, Dave, but it still doesn't work. It still stops at the End If. Do you need more information about the worksheet (for example, it contains a mixture of unicode fonts and non-unicode fonts). Is that what's causing the problem? As you can see, I am a new user and I'm unable to program a macro. "Dave Peterson" wrote: Your macro is changing the font for the cells in the empty rows. That means that you really won't see anything until you type something in one of those cells. If you really wanted to change the rowheight of those empty rows to 8, you could use: Option Explicit Sub Reduce_Height_of_Empty_Rows2() Dim LastRow As Long Dim I As Long LastRow = Cells.SpecialCells(xlLastCell).Row For I = LastRow To 2 Step -1 If Application.CountA(Rows(I)) = 0 Then Rows(I).RowHeight = 8 End If Next I End Sub Rebecca wrote: I am using MS Excel 2007. A month or so ago I asked for help to reduce the size of blank rows (currently 16 points) down to 8 points. The Excel experts advised me to try this macro: 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 Whenever I run this macro, it doesn't do the job intended (it's hard to explain -- because I know nothing about programming -- but it looks like the macro is running in loops -- the rows appear to be stuttering, but not reducing the size from 16 to 8), and when I press Escape, the debug is yellow highlight at the "End IF" line. As I mentioned above I know absolutely nothing about macro creation, so I am at a loss as to what the problem is. Could one of the MVP Excel experts take a quick look again at the macro above and give me some advice? Thank you. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Reduce Size of Blank Rows (again)
Thanks, Dave, but for some reason the macro doesn't do the job. As with the
previous macros, when I run the macro you provided, the cells flicker / blink (the macro seems to be endlessly louping and nothing is changed), and when I press ESC (because nothing is happening, other than the blinking), the MS Visual Basic window opens ("code execution has been interrupted"), and when I click on debug, the yellow hightlight is on End If in the code. This seems like a very simply problem to solve (reducing the 16-point height of empty rows), but there must be something specific to the data on my computer that is causing the problem. I apologize for this taking up some much of your time, but being a newbie I will never be able to create a macro on my own. Do you need any specific information about the column and the data contained in the cells? "Dave Peterson" wrote: Actually, the macro you got from David McRitchie changed the font size. (The macro that I suggested changed the rowheight--not what you really wanted, so discard it.) Changing the fontsize of those empty rows should change the rowheight, too--but if you've manually adjusted the rowheight of any row, then that rowheight won't be changed. You could autofit the rows--either all at once or one at a time. All at once will affect the all the rows--even the non-empty rows. Option Explicit Sub Reduce_Height_of_Empty_Rows2() '-- 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(Rows(I)) = 0 Then Rows(I).Font.Size = 8 End If Next I ActiveSheet.Rows.AutoFit End Sub If you don't want to change the height of the rows with data, you could use: Option Explicit Sub Reduce_Height_of_Empty_Rows3() '-- 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(Rows(I)) = 0 Then Rows(I).Font.Size = 8 Rows(I).autofit End If Next I End Sub I changed David McRitchies code in a couple of spots. cells(I,1).entirerow.... can be rewritten as: rows(i)... Rebecca wrote: Dave, I apologize for not being more specific. I am using MS Excel 2007. I have a column in a worksheet that contains rows (of course), some of which contain text (a mixture of unicode and non-unicode), some of which are blank (empty / no text). The entire column contains material that was pasted in from external data, and everything is in entire column 16-point font (the heights of the rows varies according to the content of the cells. I want to reduce the size (height) of the empty / blank rows in the entire column to 8-points, that is half the current height. Now when if I select the current 16-point row and select 8-point font, the height of the row is thereby reduced (of course). Apparently (I could be wrong) the macro you provided is aimed at reducing the font size of the contents of the rows, not the height, but shouldn't the height reduction happen anyway? To repeat: I want to reduce the size /height of the rows, from the current 16-point (the entire column) to 8-point (half the size). This applies only to those rows that are empty of text. When I run the macro you created, the worksheet column flickers / blinks and appear to be louping (it acts like a macro that is struggling -- how is this for a newbie explanation?). When I press ESCAPE, the debug window show a yellow highlight at the "End If" line of the macro (and nothing has been changed in the column). I could do a Camtasia so you could see what's happening, if you think my explanation above it inadequate. Again, thanks for your help. "Dave Peterson" wrote: First, what are you trying to do? Are you changing the font size (for the characters) or the rowheight? Second, I don't understand what "It still stops at the End If" statement means. Maybe you could explain what you want and what is happening in a little more detail. Rebecca wrote: Thanks, Dave, but it still doesn't work. It still stops at the End If. Do you need more information about the worksheet (for example, it contains a mixture of unicode fonts and non-unicode fonts). Is that what's causing the problem? As you can see, I am a new user and I'm unable to program a macro. "Dave Peterson" wrote: Your macro is changing the font for the cells in the empty rows. That means that you really won't see anything until you type something in one of those cells. If you really wanted to change the rowheight of those empty rows to 8, you could use: Option Explicit Sub Reduce_Height_of_Empty_Rows2() Dim LastRow As Long Dim I As Long LastRow = Cells.SpecialCells(xlLastCell).Row For I = LastRow To 2 Step -1 If Application.CountA(Rows(I)) = 0 Then Rows(I).RowHeight = 8 End If Next I End Sub Rebecca wrote: I am using MS Excel 2007. A month or so ago I asked for help to reduce the size of blank rows (currently 16 points) down to 8 points. The Excel experts advised me to try this macro: 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 Whenever I run this macro, it doesn't do the job intended (it's hard to explain -- because I know nothing about programming -- but it looks like the macro is running in loops -- the rows appear to be stuttering, but not reducing the size from 16 to 8), and when I press Escape, the debug is yellow highlight at the "End IF" line. As I mentioned above I know absolutely nothing about macro creation, so I am at a loss as to what the problem is. Could one of the MVP Excel experts take a quick look again at the macro above and give me some advice? Thank you. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Reduce Size of Blank Rows (again)
If you hit ctrl-End, what cell do you go to.
I'm guessing that your used range is way past what you think it should be. And the flickering is caused by stuff getting changed way past where your data is. Visit Debra Dalgleish's site: http://contextures.com/xlfaqApp.html#Unused to find a way to reset that last used cell. Rebecca wrote: Thanks, Dave, but for some reason the macro doesn't do the job. As with the previous macros, when I run the macro you provided, the cells flicker / blink (the macro seems to be endlessly louping and nothing is changed), and when I press ESC (because nothing is happening, other than the blinking), the MS Visual Basic window opens ("code execution has been interrupted"), and when I click on debug, the yellow hightlight is on End If in the code. This seems like a very simply problem to solve (reducing the 16-point height of empty rows), but there must be something specific to the data on my computer that is causing the problem. I apologize for this taking up some much of your time, but being a newbie I will never be able to create a macro on my own. Do you need any specific information about the column and the data contained in the cells? "Dave Peterson" wrote: Actually, the macro you got from David McRitchie changed the font size. (The macro that I suggested changed the rowheight--not what you really wanted, so discard it.) Changing the fontsize of those empty rows should change the rowheight, too--but if you've manually adjusted the rowheight of any row, then that rowheight won't be changed. You could autofit the rows--either all at once or one at a time. All at once will affect the all the rows--even the non-empty rows. Option Explicit Sub Reduce_Height_of_Empty_Rows2() '-- 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(Rows(I)) = 0 Then Rows(I).Font.Size = 8 End If Next I ActiveSheet.Rows.AutoFit End Sub If you don't want to change the height of the rows with data, you could use: Option Explicit Sub Reduce_Height_of_Empty_Rows3() '-- 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(Rows(I)) = 0 Then Rows(I).Font.Size = 8 Rows(I).autofit End If Next I End Sub I changed David McRitchies code in a couple of spots. cells(I,1).entirerow.... can be rewritten as: rows(i)... Rebecca wrote: Dave, I apologize for not being more specific. I am using MS Excel 2007. I have a column in a worksheet that contains rows (of course), some of which contain text (a mixture of unicode and non-unicode), some of which are blank (empty / no text). The entire column contains material that was pasted in from external data, and everything is in entire column 16-point font (the heights of the rows varies according to the content of the cells. I want to reduce the size (height) of the empty / blank rows in the entire column to 8-points, that is half the current height. Now when if I select the current 16-point row and select 8-point font, the height of the row is thereby reduced (of course). Apparently (I could be wrong) the macro you provided is aimed at reducing the font size of the contents of the rows, not the height, but shouldn't the height reduction happen anyway? To repeat: I want to reduce the size /height of the rows, from the current 16-point (the entire column) to 8-point (half the size). This applies only to those rows that are empty of text. When I run the macro you created, the worksheet column flickers / blinks and appear to be louping (it acts like a macro that is struggling -- how is this for a newbie explanation?). When I press ESCAPE, the debug window show a yellow highlight at the "End If" line of the macro (and nothing has been changed in the column). I could do a Camtasia so you could see what's happening, if you think my explanation above it inadequate. Again, thanks for your help. "Dave Peterson" wrote: First, what are you trying to do? Are you changing the font size (for the characters) or the rowheight? Second, I don't understand what "It still stops at the End If" statement means. Maybe you could explain what you want and what is happening in a little more detail. Rebecca wrote: Thanks, Dave, but it still doesn't work. It still stops at the End If. Do you need more information about the worksheet (for example, it contains a mixture of unicode fonts and non-unicode fonts). Is that what's causing the problem? As you can see, I am a new user and I'm unable to program a macro. "Dave Peterson" wrote: Your macro is changing the font for the cells in the empty rows. That means that you really won't see anything until you type something in one of those cells. If you really wanted to change the rowheight of those empty rows to 8, you could use: Option Explicit Sub Reduce_Height_of_Empty_Rows2() Dim LastRow As Long Dim I As Long LastRow = Cells.SpecialCells(xlLastCell).Row For I = LastRow To 2 Step -1 If Application.CountA(Rows(I)) = 0 Then Rows(I).RowHeight = 8 End If Next I End Sub Rebecca wrote: I am using MS Excel 2007. A month or so ago I asked for help to reduce the size of blank rows (currently 16 points) down to 8 points. The Excel experts advised me to try this macro: 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 Whenever I run this macro, it doesn't do the job intended (it's hard to explain -- because I know nothing about programming -- but it looks like the macro is running in loops -- the rows appear to be stuttering, but not reducing the size from 16 to 8), and when I press Escape, the debug is yellow highlight at the "End IF" line. As I mentioned above I know absolutely nothing about macro creation, so I am at a loss as to what the problem is. Could one of the MVP Excel experts take a quick look again at the macro above and give me some advice? Thank you. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Reduce Size of Blank Rows (again)
ps. And when you interrupt the code, it just hasn't gotten to your data yet.
Rebecca wrote: Thanks, Dave, but for some reason the macro doesn't do the job. As with the previous macros, when I run the macro you provided, the cells flicker / blink (the macro seems to be endlessly louping and nothing is changed), and when I press ESC (because nothing is happening, other than the blinking), the MS Visual Basic window opens ("code execution has been interrupted"), and when I click on debug, the yellow hightlight is on End If in the code. This seems like a very simply problem to solve (reducing the 16-point height of empty rows), but there must be something specific to the data on my computer that is causing the problem. I apologize for this taking up some much of your time, but being a newbie I will never be able to create a macro on my own. Do you need any specific information about the column and the data contained in the cells? "Dave Peterson" wrote: Actually, the macro you got from David McRitchie changed the font size. (The macro that I suggested changed the rowheight--not what you really wanted, so discard it.) Changing the fontsize of those empty rows should change the rowheight, too--but if you've manually adjusted the rowheight of any row, then that rowheight won't be changed. You could autofit the rows--either all at once or one at a time. All at once will affect the all the rows--even the non-empty rows. Option Explicit Sub Reduce_Height_of_Empty_Rows2() '-- 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(Rows(I)) = 0 Then Rows(I).Font.Size = 8 End If Next I ActiveSheet.Rows.AutoFit End Sub If you don't want to change the height of the rows with data, you could use: Option Explicit Sub Reduce_Height_of_Empty_Rows3() '-- 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(Rows(I)) = 0 Then Rows(I).Font.Size = 8 Rows(I).autofit End If Next I End Sub I changed David McRitchies code in a couple of spots. cells(I,1).entirerow.... can be rewritten as: rows(i)... Rebecca wrote: Dave, I apologize for not being more specific. I am using MS Excel 2007. I have a column in a worksheet that contains rows (of course), some of which contain text (a mixture of unicode and non-unicode), some of which are blank (empty / no text). The entire column contains material that was pasted in from external data, and everything is in entire column 16-point font (the heights of the rows varies according to the content of the cells. I want to reduce the size (height) of the empty / blank rows in the entire column to 8-points, that is half the current height. Now when if I select the current 16-point row and select 8-point font, the height of the row is thereby reduced (of course). Apparently (I could be wrong) the macro you provided is aimed at reducing the font size of the contents of the rows, not the height, but shouldn't the height reduction happen anyway? To repeat: I want to reduce the size /height of the rows, from the current 16-point (the entire column) to 8-point (half the size). This applies only to those rows that are empty of text. When I run the macro you created, the worksheet column flickers / blinks and appear to be louping (it acts like a macro that is struggling -- how is this for a newbie explanation?). When I press ESCAPE, the debug window show a yellow highlight at the "End If" line of the macro (and nothing has been changed in the column). I could do a Camtasia so you could see what's happening, if you think my explanation above it inadequate. Again, thanks for your help. "Dave Peterson" wrote: First, what are you trying to do? Are you changing the font size (for the characters) or the rowheight? Second, I don't understand what "It still stops at the End If" statement means. Maybe you could explain what you want and what is happening in a little more detail. Rebecca wrote: Thanks, Dave, but it still doesn't work. It still stops at the End If. Do you need more information about the worksheet (for example, it contains a mixture of unicode fonts and non-unicode fonts). Is that what's causing the problem? As you can see, I am a new user and I'm unable to program a macro. "Dave Peterson" wrote: Your macro is changing the font for the cells in the empty rows. That means that you really won't see anything until you type something in one of those cells. If you really wanted to change the rowheight of those empty rows to 8, you could use: Option Explicit Sub Reduce_Height_of_Empty_Rows2() Dim LastRow As Long Dim I As Long LastRow = Cells.SpecialCells(xlLastCell).Row For I = LastRow To 2 Step -1 If Application.CountA(Rows(I)) = 0 Then Rows(I).RowHeight = 8 End If Next I End Sub Rebecca wrote: I am using MS Excel 2007. A month or so ago I asked for help to reduce the size of blank rows (currently 16 points) down to 8 points. The Excel experts advised me to try this macro: 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 Whenever I run this macro, it doesn't do the job intended (it's hard to explain -- because I know nothing about programming -- but it looks like the macro is running in loops -- the rows appear to be stuttering, but not reducing the size from 16 to 8), and when I press Escape, the debug is yellow highlight at the "End IF" line. As I mentioned above I know absolutely nothing about macro creation, so I am at a loss as to what the problem is. Could one of the MVP Excel experts take a quick look again at the macro above and give me some advice? Thank you. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Reduce Size of Blank Rows (again)
Maybe you just do it manually.
Select a column and F5SpecialBlanksOK FormatRowHeight...8 Gord Dibben MS Excel MVP On Sat, 29 Mar 2008 08:59:00 -0700, Rebecca wrote: Thanks, Dave, but for some reason the macro doesn't do the job. As with the previous macros, when I run the macro you provided, the cells flicker / blink (the macro seems to be endlessly louping and nothing is changed), and when I press ESC (because nothing is happening, other than the blinking), the MS Visual Basic window opens ("code execution has been interrupted"), and when I click on debug, the yellow hightlight is on End If in the code. This seems like a very simply problem to solve (reducing the 16-point height of empty rows), but there must be something specific to the data on my computer that is causing the problem. I apologize for this taking up some much of your time, but being a newbie I will never be able to create a macro on my own. Do you need any specific information about the column and the data contained in the cells? "Dave Peterson" wrote: Actually, the macro you got from David McRitchie changed the font size. (The macro that I suggested changed the rowheight--not what you really wanted, so discard it.) Changing the fontsize of those empty rows should change the rowheight, too--but if you've manually adjusted the rowheight of any row, then that rowheight won't be changed. You could autofit the rows--either all at once or one at a time. All at once will affect the all the rows--even the non-empty rows. Option Explicit Sub Reduce_Height_of_Empty_Rows2() '-- 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(Rows(I)) = 0 Then Rows(I).Font.Size = 8 End If Next I ActiveSheet.Rows.AutoFit End Sub If you don't want to change the height of the rows with data, you could use: Option Explicit Sub Reduce_Height_of_Empty_Rows3() '-- 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(Rows(I)) = 0 Then Rows(I).Font.Size = 8 Rows(I).autofit End If Next I End Sub I changed David McRitchies code in a couple of spots. cells(I,1).entirerow.... can be rewritten as: rows(i)... Rebecca wrote: Dave, I apologize for not being more specific. I am using MS Excel 2007. I have a column in a worksheet that contains rows (of course), some of which contain text (a mixture of unicode and non-unicode), some of which are blank (empty / no text). The entire column contains material that was pasted in from external data, and everything is in entire column 16-point font (the heights of the rows varies according to the content of the cells. I want to reduce the size (height) of the empty / blank rows in the entire column to 8-points, that is half the current height. Now when if I select the current 16-point row and select 8-point font, the height of the row is thereby reduced (of course). Apparently (I could be wrong) the macro you provided is aimed at reducing the font size of the contents of the rows, not the height, but shouldn't the height reduction happen anyway? To repeat: I want to reduce the size /height of the rows, from the current 16-point (the entire column) to 8-point (half the size). This applies only to those rows that are empty of text. When I run the macro you created, the worksheet column flickers / blinks and appear to be louping (it acts like a macro that is struggling -- how is this for a newbie explanation?). When I press ESCAPE, the debug window show a yellow highlight at the "End If" line of the macro (and nothing has been changed in the column). I could do a Camtasia so you could see what's happening, if you think my explanation above it inadequate. Again, thanks for your help. "Dave Peterson" wrote: First, what are you trying to do? Are you changing the font size (for the characters) or the rowheight? Second, I don't understand what "It still stops at the End If" statement means. Maybe you could explain what you want and what is happening in a little more detail. Rebecca wrote: Thanks, Dave, but it still doesn't work. It still stops at the End If. Do you need more information about the worksheet (for example, it contains a mixture of unicode fonts and non-unicode fonts). Is that what's causing the problem? As you can see, I am a new user and I'm unable to program a macro. "Dave Peterson" wrote: Your macro is changing the font for the cells in the empty rows. That means that you really won't see anything until you type something in one of those cells. If you really wanted to change the rowheight of those empty rows to 8, you could use: Option Explicit Sub Reduce_Height_of_Empty_Rows2() Dim LastRow As Long Dim I As Long LastRow = Cells.SpecialCells(xlLastCell).Row For I = LastRow To 2 Step -1 If Application.CountA(Rows(I)) = 0 Then Rows(I).RowHeight = 8 End If Next I End Sub Rebecca wrote: I am using MS Excel 2007. A month or so ago I asked for help to reduce the size of blank rows (currently 16 points) down to 8 points. The Excel experts advised me to try this macro: 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 Whenever I run this macro, it doesn't do the job intended (it's hard to explain -- because I know nothing about programming -- but it looks like the macro is running in loops -- the rows appear to be stuttering, but not reducing the size from 16 to 8), and when I press Escape, the debug is yellow highlight at the "End IF" line. As I mentioned above I know absolutely nothing about macro creation, so I am at a loss as to what the problem is. Could one of the MVP Excel experts take a quick look again at the macro above and give me some advice? Thank you. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Reduce Size of Blank Rows (again)
The method Gord just posted worked best for me.
--JP On Mar 29, 11:59*am, Rebecca wrote: Thanks, Dave, but for some reason the macro doesn't do the job. As with the previous macros, when I run the macro you provided, the cells flicker / blink (the macro seems to be endlessly louping and nothing is changed), and when I press ESC (because nothing is happening, other than the blinking), the MS Visual Basic window opens ("code execution has been interrupted"), and when I click on debug, the yellow hightlight is on End If in the code. This seems like a very simply problem to solve (reducing the 16-point height of empty rows), but there must be something specific to the data on my computer that is causing the problem. I apologize for this taking up some much of your time, but being a newbie I will never be able to create a macro on my own. Do you need any specific information about the column and the data contained in the cells? "Dave Peterson" wrote: Actually, the macro you got from David McRitchie changed the font size. *(The macro that I suggested changed the rowheight--not what you really wanted, so discard it.) Changing the fontsize of those empty rows should change the rowheight, too--but if you've manually adjusted the rowheight of any row, then that rowheight won't be changed. You could autofit the rows--either all at once or one at a time. All at once will affect the all the rows--even the non-empty rows. Option Explicit Sub Reduce_Height_of_Empty_Rows2() * *'-- 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(Rows(I)) = 0 Then * * * * *Rows(I).Font.Size = 8 * * * End If * Next I * ActiveSheet.Rows.AutoFit End Sub If you don't want to change the height of the rows with data, you could use: Option Explicit Sub Reduce_Height_of_Empty_Rows3() * *'-- 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(Rows(I)) = 0 Then * * * * *Rows(I).Font.Size = 8 * * * * *Rows(I).autofit * * * End If * Next I End Sub I changed David McRitchies code in a couple of spots. cells(I,1).entirerow.... can be rewritten as: rows(i)... Rebecca wrote: Dave, I apologize for not being more specific. I am using MS Excel 2007. I have a column in a worksheet that contains rows (of course), some of which contain text (a mixture of unicode and non-unicode), some of which are blank (empty / no text). The entire column contains material that was pasted in from external data, and everything is in entire column 16-point font (the heights of the rows varies according to the content of the cells. *I want to reduce the size (height) of the empty / blank rows in the entire column to 8-points, that is half the current height. Now when if I select the current 16-point row and select 8-point font, the height of the row is thereby reduced (of course). Apparently (I could be wrong) the macro you provided is aimed at reducing the font size of the contents of the rows, not the height, but shouldn't the height reduction happen anyway? To repeat: I want to reduce the size /height of the rows, from the current 16-point (the entire column) to 8-point (half the size). This applies only to those rows that are empty of text. When I run the macro you created, the worksheet column flickers / blinks and appear to be louping (it acts like a macro that is struggling -- how is this for a newbie explanation?). When I press ESCAPE, the debug window show a yellow highlight at the "End If" line of the macro (and nothing has been changed in the column). *I could do a Camtasia so you could see what's happening, if you think my explanation above it inadequate. Again, thanks for your help. "Dave Peterson" wrote: First, what are you trying to do? Are you changing the font size (for the characters) or the rowheight? Second, I don't understand what "It still stops at the End If" statement means. Maybe you could explain what you want and what is happening in a little more detail. Rebecca wrote: Thanks, Dave, but it still doesn't work. It still stops at the End If. Do you need more information about the worksheet (for example, it contains a mixture of unicode fonts and non-unicode fonts). Is that what's causing the problem? As you can see, I am a new user and I'm unable to program a macro. "Dave Peterson" wrote: Your macro is changing the font for the cells in the empty rows. That means that you really won't see anything until you type something in one of those cells. If you really wanted to change the rowheight of those empty rows to 8, you could use: Option Explicit Sub Reduce_Height_of_Empty_Rows2() * Dim LastRow As Long * Dim I As Long * LastRow = Cells.SpecialCells(xlLastCell).Row * For I = LastRow To 2 Step -1 * * * If Application.CountA(Rows(I)) = 0 Then * * * * *Rows(I).RowHeight = 8 * * * End If * Next I End Sub Rebecca wrote: I am using MS Excel 2007. A month or so ago I asked for help to reduce the size of blank rows (currently 16 points) down to 8 points. The Excel experts advised me to try this macro: 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 Whenever I run this macro, it doesn't do the job intended (it's hard to explain -- because I know nothing about programming -- but it looks like the macro is running in loops -- the rows appear to be stuttering, but not reducing the size from 16 to 8), and when I press Escape, the debug is yellow highlight at the "End IF" line. As I mentioned above I know absolutely nothing about macro creation, so I am at a loss as to what the problem is. Could one of the MVP Excel experts take a quick look again at the macro above and give me some advice? Thank you. -- Dave Peterson -- Dave Peterson -- Dave Peterson- Hide quoted text - - Show quoted text - |
#13
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Reduce Size of Blank Rows (again)
Yikes! I guess there is more than one way to skin a cat. Thanks a million
Gord, and you, too, Dave. These Excel discussion groups are indispensible, as is the software itself. "JP" wrote: The method Gord just posted worked best for me. --JP On Mar 29, 11:59 am, Rebecca wrote: Thanks, Dave, but for some reason the macro doesn't do the job. As with the previous macros, when I run the macro you provided, the cells flicker / blink (the macro seems to be endlessly louping and nothing is changed), and when I press ESC (because nothing is happening, other than the blinking), the MS Visual Basic window opens ("code execution has been interrupted"), and when I click on debug, the yellow hightlight is on End If in the code. This seems like a very simply problem to solve (reducing the 16-point height of empty rows), but there must be something specific to the data on my computer that is causing the problem. I apologize for this taking up some much of your time, but being a newbie I will never be able to create a macro on my own. Do you need any specific information about the column and the data contained in the cells? "Dave Peterson" wrote: Actually, the macro you got from David McRitchie changed the font size. (The macro that I suggested changed the rowheight--not what you really wanted, so discard it.) Changing the fontsize of those empty rows should change the rowheight, too--but if you've manually adjusted the rowheight of any row, then that rowheight won't be changed. You could autofit the rows--either all at once or one at a time. All at once will affect the all the rows--even the non-empty rows. Option Explicit Sub Reduce_Height_of_Empty_Rows2() '-- 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(Rows(I)) = 0 Then Rows(I).Font.Size = 8 End If Next I ActiveSheet.Rows.AutoFit End Sub If you don't want to change the height of the rows with data, you could use: Option Explicit Sub Reduce_Height_of_Empty_Rows3() '-- 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(Rows(I)) = 0 Then Rows(I).Font.Size = 8 Rows(I).autofit End If Next I End Sub I changed David McRitchies code in a couple of spots. cells(I,1).entirerow.... can be rewritten as: rows(i)... Rebecca wrote: Dave, I apologize for not being more specific. I am using MS Excel 2007. I have a column in a worksheet that contains rows (of course), some of which contain text (a mixture of unicode and non-unicode), some of which are blank (empty / no text). The entire column contains material that was pasted in from external data, and everything is in entire column 16-point font (the heights of the rows varies according to the content of the cells. I want to reduce the size (height) of the empty / blank rows in the entire column to 8-points, that is half the current height. Now when if I select the current 16-point row and select 8-point font, the height of the row is thereby reduced (of course). Apparently (I could be wrong) the macro you provided is aimed at reducing the font size of the contents of the rows, not the height, but shouldn't the height reduction happen anyway? To repeat: I want to reduce the size /height of the rows, from the current 16-point (the entire column) to 8-point (half the size). This applies only to those rows that are empty of text. When I run the macro you created, the worksheet column flickers / blinks and appear to be louping (it acts like a macro that is struggling -- how is this for a newbie explanation?). When I press ESCAPE, the debug window show a yellow highlight at the "End If" line of the macro (and nothing has been changed in the column). I could do a Camtasia so you could see what's happening, if you think my explanation above it inadequate. Again, thanks for your help. "Dave Peterson" wrote: First, what are you trying to do? Are you changing the font size (for the characters) or the rowheight? Second, I don't understand what "It still stops at the End If" statement means. Maybe you could explain what you want and what is happening in a little more detail. Rebecca wrote: Thanks, Dave, but it still doesn't work. It still stops at the End If. Do you need more information about the worksheet (for example, it contains a mixture of unicode fonts and non-unicode fonts). Is that what's causing the problem? As you can see, I am a new user and I'm unable to program a macro. "Dave Peterson" wrote: Your macro is changing the font for the cells in the empty rows. That means that you really won't see anything until you type something in one of those cells. If you really wanted to change the rowheight of those empty rows to 8, you could use: Option Explicit Sub Reduce_Height_of_Empty_Rows2() Dim LastRow As Long Dim I As Long LastRow = Cells.SpecialCells(xlLastCell).Row For I = LastRow To 2 Step -1 If Application.CountA(Rows(I)) = 0 Then Rows(I).RowHeight = 8 End If Next I End Sub Rebecca wrote: I am using MS Excel 2007. A month or so ago I asked for help to reduce the size of blank rows (currently 16 points) down to 8 points. The Excel experts advised me to try this macro: 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 Whenever I run this macro, it doesn't do the job intended (it's hard to explain -- because I know nothing about programming -- but it looks like the macro is running in loops -- the rows appear to be stuttering, but not reducing the size from 16 to 8), and when I press Escape, the debug is yellow highlight at the "End IF" line. As I mentioned above I know absolutely nothing about macro creation, so I am at a loss as to what the problem is. Could one of the MVP Excel experts take a quick look again at the macro above and give me some advice? Thank you. -- Dave Peterson -- Dave Peterson -- Dave Peterson- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reduce Size of Blank Rows | New Users to Excel | |||
Toolbars - how to reduce size of? | Excel Discussion (Misc queries) | |||
how to set sheet size to reduce file size | Excel Discussion (Misc queries) | |||
reduce file size | Excel Discussion (Misc queries) | |||
Reduce size file | Excel Discussion (Misc queries) |