If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Macro for hiding rows
Hi K,
1) I guess the task is to hide not to delete Thank you - yes I mis-read! so probably: Columns(2).SpecialCells(xlCellTypeBlanks).EntireRo w.Hidden=True With the qualification of my follow-up post that columns(2) should read Columns(1), for obvious reasons. 2) This method has a limitation of max 8,192 non-contiguous cells I am aware of the problem and have frequently referred to this in other posts. It should be noted, however, that the constraining factor is independent of the number of blank cells. To demonstrate this try: '============= Public Sub Demo() Dim i As Long Dim rng As Range, rng1 As Range Application.ScreenUpdating = False Set rng = Range("A1").Resize(8192 * 3 - 2) rng.Value = "XYZ" For i = 1 To rng.Count Step 3 Cells(i, 1).Resize(2).Clear Next i Set rng1 = rng.SpecialCells(xlCellTypeBlanks) rng1.Interior.ColorIndex = 6 Debug.Print "rng1.Cells.Count ", rng1.Cells.Count Debug.Print "rng1.Areas.Count ", rng1.Areas.Count Application.ScreenUpdating = True End Sub '============= --- Regards, Norman "KL" wrote in message ... just to add two comments: 1) I guess the task is to hide not to delete, so probably: Columns(2).SpecialCells(xlCellTypeBlanks).EntireRo w.Hidden=True 2) This method has a limitation of max 8,192 non-contiguous cells (otherwise it includes the whole column/row/sheet): http://support.microsoft.com/kb/832293/en-us For this to become an issue in this specific example one needs to have at least 16385 rows where every other row is blank (so the risk might be remote). Regards, KL "Norman Jones" wrote in message ... Hi Luke, As an alternative, try: Try: '============= Public Sub Tester() On Error Resume Next Columns(2).SpecialCells(xlCellTypeBlanks).EntireRo w.Delete On Error GoTo 0 End Sub '============= --- Regards, Norman "Luke" wrote in message ... I have a spreadsheet which contains a lot of rows with no data, I would like to create a macro to hide these rows. Preferably I would like it to work off one column and hide any rows with a value of zero. Any ideas? |
#12
|
|||
|
|||
Macro for hiding rows
Let's back up here.
If a cell has a 0 (zero) in it, it is not blank. It looks like all or most of the suggestions offered here has been if cells are Blank. But, I see that you "prefer" if rows are 0 (zero value). Before running this macro select (highlight) your intended range, say G2:G1000 Using Selection make the macro a bit more useful. In a standard module put: Sub HideRowsWithZero For Each c In Selection If c.Value = 0 Then c.EntireRow.Hidden = True End If Next c End Sub Hope that Helps, "KL" wrote in message ... another way without macros: 1) select one of the columns where empty cells correspond to empty rows 2) menu EditGoto... 3) press the button 'Special...' 4) mark the 'Blanks' option and press 'OK' 5) menu FormatRowsHide... or using short cut keys: 1) having the cursor in a cell of the relevant column press Ctrl+Space 2) Ctrl+g 3) Hold Alt+ s, k, 4) Enter 5) Hold Alt + o, r, h Regards, KL "Luke" wrote in message ... I have a spreadsheet which contains a lot of rows with no data, I would like to create a macro to hide these rows. Preferably I would like it to work off one column and hide any rows with a value of zero. Any ideas? |
#13
|
|||
|
|||
Macro for hiding rows
Hi Jim,
It looks like all or most of the suggestions offered here has been if cells are Blank. But, I see that you "prefer" if rows are 0 (zero value). Granted that there is potential ambiguity, my response was predicated on the OP's statement: I have a spreadsheet which contains a lot of rows with no data. Which interpretation is correct can only be resolved by the OP, but at least he now has solutions to deal with either eventuality. --- Regards, Norman "JMay" wrote in message newsVSqf.84061$WH.53867@dukeread01... Let's back up here. If a cell has a 0 (zero) in it, it is not blank. Before running this macro select (highlight) your intended range, say G2:G1000 Using Selection make the macro a bit more useful. In a standard module put: Sub HideRowsWithZero For Each c In Selection If c.Value = 0 Then c.EntireRow.Hidden = True End If Next c End Sub Hope that Helps, "KL" wrote in message ... another way without macros: 1) select one of the columns where empty cells correspond to empty rows 2) menu EditGoto... 3) press the button 'Special...' 4) mark the 'Blanks' option and press 'OK' 5) menu FormatRowsHide... or using short cut keys: 1) having the cursor in a cell of the relevant column press Ctrl+Space 2) Ctrl+g 3) Hold Alt+ s, k, 4) Enter 5) Hold Alt + o, r, h Regards, KL "Luke" wrote in message ... I have a spreadsheet which contains a lot of rows with no data, I would like to create a macro to hide these rows. Preferably I would like it to work off one column and hide any rows with a value of zero. Any ideas? |
#14
|
|||
|
|||
Macro for hiding rows
Hi Norman,
It should be noted, however, that the constraining factor is independent of the number of blank cells. To demonstrate this try: Oh yes it is, but not only of the number of blank cells but the way they are distributed. As I said in my previous message "for this to become an issue in this specific example one needs to have at least 16385 rows where every other row is blank..." In other words there need to be more than 8192 areas. Your code returns exactly 8192 areas, so try this one (and if you still believe that it is independent of the # of blank cells, try reproducing the problem with =8192 blank cells): Public Sub Demo() Dim i As Long Dim rng As Range, rng1 As Range Application.ScreenUpdating = False Set rng = Range("A1").Resize(8192 * 3 - 1) rng.Value = "XYZ" For i = 1 To rng.Count Step 3 Cells(i, 1).Resize(2).Clear Next i Set rng1 = rng.SpecialCells(xlCellTypeBlanks) MsgBox rng1.Areas.Count Application.ScreenUpdating = True Exit Sub End Sub Regards, KL |
#15
|
|||
|
|||
Macro for hiding rows
Hi K,
I suspect that you misunderstand both my statement and the purpose of my post, which had no polemical intent,. I am well aware of the distinction between areas and cells and the relevance of these to the use of the SpecialCells method. As I indicated, I have referred to this precise issue in numerous previous posts, My response, which was primarily aimed at the OP, was intended to indicate that the number of blank cells was not a limiting issue. I was concerned that your use of the expression: This method has a limitation of max 8,192 non-contiguous cells without any reference to areas or ranges might be interpreted as indicating a limit to the number of blank cells. This concern was not alleviated by your subsequent statement: For this to become an issue in this specific example one needs to have at least 16385 rows where every other row is blank (so the risk might be remote). which, as it implicitly refers to 8192 blank cells, might, I felt, serve to reinforce any misconception. I therefore provided an example of a viable use of the SpecialCells method in which the range of interest comprised over 16k blank cells. Obviously, the example could readily be adapted to produce analogous results with much higher numbers of blank cells. The question of the number of areas was not an issue: indeed the only reference to areas and, implicitly, their relevance, was in my post. It was, as I have indicated, this contextual omission which prompted my response. --- Regards, Norman "KL" wrote in message ... Hi Norman, It should be noted, however, that the constraining factor is independent of the number of blank cells. To demonstrate this try: Oh yes it is, but not only of the number of blank cells but the way they are distributed. As I said in my previous message "for this to become an issue in this specific example one needs to have at least 16385 rows where every other row is blank..." In other words there need to be more than 8192 areas. Your code returns exactly 8192 areas, so try this one (and if you still believe that it is independent of the # of blank cells, try reproducing the problem with =8192 blank cells): Public Sub Demo() Dim i As Long Dim rng As Range, rng1 As Range Application.ScreenUpdating = False Set rng = Range("A1").Resize(8192 * 3 - 1) rng.Value = "XYZ" For i = 1 To rng.Count Step 3 Cells(i, 1).Resize(2).Clear Next i Set rng1 = rng.SpecialCells(xlCellTypeBlanks) MsgBox rng1.Areas.Count Application.ScreenUpdating = True Exit Sub End Sub Regards, KL |
#16
|
|||
|
|||
Macro for hiding rows
OK let's attribute this to my poor command of English, but just for clarity "This method has a limitation of max 8,192
non-contiguous cells" is not mine but rather Microsoft's :-) English isn't my native language, so I am sure you can explain to me how can "8,192 non-contiguous cells" mean anything different to "8,192 areas" in Excel. Best regards and Merry X-Mas KL "Norman Jones" wrote in message ... Hi K, I suspect that you misunderstand both my statement and the purpose of my post, which had no polemical intent,. I am well aware of the distinction between areas and cells and the relevance of these to the use of the SpecialCells method. As I indicated, I have referred to this precise issue in numerous previous posts, My response, which was primarily aimed at the OP, was intended to indicate that the number of blank cells was not a limiting issue. I was concerned that your use of the expression: This method has a limitation of max 8,192 non-contiguous cells without any reference to areas or ranges might be interpreted as indicating a limit to the number of blank cells. This concern was not alleviated by your subsequent statement: For this to become an issue in this specific example one needs to have at least 16385 rows where every other row is blank (so the risk might be remote). which, as it implicitly refers to 8192 blank cells, might, I felt, serve to reinforce any misconception. I therefore provided an example of a viable use of the SpecialCells method in which the range of interest comprised over 16k blank cells. Obviously, the example could readily be adapted to produce analogous results with much higher numbers of blank cells. The question of the number of areas was not an issue: indeed the only reference to areas and, implicitly, their relevance, was in my post. It was, as I have indicated, this contextual omission which prompted my response. --- Regards, Norman "KL" wrote in message ... Hi Norman, It should be noted, however, that the constraining factor is independent of the number of blank cells. To demonstrate this try: Oh yes it is, but not only of the number of blank cells but the way they are distributed. As I said in my previous message "for this to become an issue in this specific example one needs to have at least 16385 rows where every other row is blank..." In other words there need to be more than 8192 areas. Your code returns exactly 8192 areas, so try this one (and if you still believe that it is independent of the # of blank cells, try reproducing the problem with =8192 blank cells): Public Sub Demo() Dim i As Long Dim rng As Range, rng1 As Range Application.ScreenUpdating = False Set rng = Range("A1").Resize(8192 * 3 - 1) rng.Value = "XYZ" For i = 1 To rng.Count Step 3 Cells(i, 1).Resize(2).Clear Next i Set rng1 = rng.SpecialCells(xlCellTypeBlanks) MsgBox rng1.Areas.Count Application.ScreenUpdating = True Exit Sub End Sub Regards, KL |
#17
|
|||
|
|||
Macro for hiding rows
Hi KL,
My interest was not a matter of semantics but uniquely the question of possible (mis)interpretation: the mere fact that cell and area may be interpreted differently might have some bearing. Before leaving this matter, I note that I have omitted to respond to your challenge: Your code returns exactly 8192 areas, so try this one (and if you still believe that it is independent of the # of blank cells, try reproducing the problem with =8192 blank cells): Let me, therefore, rectify: try this version of the previously suggested code: '============= Public Sub Demo2() Dim i As Long Dim rng As Range, rng1 As Range, rng2 As Range Application.ScreenUpdating = False Set rng = Range("A1:A16384") rng.Value = "XYZ" For i = 1 To rng.Rows.Count Step 2 rng.Rows(i).Clear Next i Set rng1 = rng.SpecialCells(xlCellTypeBlanks) Set rng2 = rng.Columns(1).SpecialCells(xlCellTypeBlanks) MsgBox "Rng1 Areas =" & rng1.Areas.Count _ & vbNewLine & _ "Rng2 Areas =" & rng2.Areas.Count Application.ScreenUpdating = True End Sub '============= --- Regards, Norman "KL" wrote in message ... OK let's attribute this to my poor command of English, but just for clarity "This method has a limitation of max 8,192 non-contiguous cells" is not mine but rather Microsoft's :-) English isn't my native language, so I am sure you can explain to me how can "8,192 non-contiguous cells" mean anything different to "8,192 areas" in Excel. Best regards and Merry X-Mas KL "Norman Jones" wrote in message ... Hi K, I suspect that you misunderstand both my statement and the purpose of my post, which had no polemical intent,. I am well aware of the distinction between areas and cells and the relevance of these to the use of the SpecialCells method. As I indicated, I have referred to this precise issue in numerous previous posts, My response, which was primarily aimed at the OP, was intended to indicate that the number of blank cells was not a limiting issue. I was concerned that your use of the expression: This method has a limitation of max 8,192 non-contiguous cells without any reference to areas or ranges might be interpreted as indicating a limit to the number of blank cells. This concern was not alleviated by your subsequent statement: For this to become an issue in this specific example one needs to have at least 16385 rows where every other row is blank (so the risk might be remote). which, as it implicitly refers to 8192 blank cells, might, I felt, serve to reinforce any misconception. I therefore provided an example of a viable use of the SpecialCells method in which the range of interest comprised over 16k blank cells. Obviously, the example could readily be adapted to produce analogous results with much higher numbers of blank cells. The question of the number of areas was not an issue: indeed the only reference to areas and, implicitly, their relevance, was in my post. It was, as I have indicated, this contextual omission which prompted my response. --- Regards, Norman "KL" wrote in message ... Hi Norman, It should be noted, however, that the constraining factor is independent of the number of blank cells. To demonstrate this try: Oh yes it is, but not only of the number of blank cells but the way they are distributed. As I said in my previous message "for this to become an issue in this specific example one needs to have at least 16385 rows where every other row is blank..." In other words there need to be more than 8192 areas. Your code returns exactly 8192 areas, so try this one (and if you still believe that it is independent of the # of blank cells, try reproducing the problem with =8192 blank cells): Public Sub Demo() Dim i As Long Dim rng As Range, rng1 As Range Application.ScreenUpdating = False Set rng = Range("A1").Resize(8192 * 3 - 1) rng.Value = "XYZ" For i = 1 To rng.Count Step 3 Cells(i, 1).Resize(2).Clear Next i Set rng1 = rng.SpecialCells(xlCellTypeBlanks) MsgBox rng1.Areas.Count Application.ScreenUpdating = True Exit Sub End Sub Regards, KL |
#18
|
|||
|
|||
Macro for hiding rows
Hi KL,
A significant typo: Set rng = Range("A1:A16384") should read: Set rng = Range("A1:B16384") --- Regards, Norman |
#19
|
|||
|
|||
Macro for hiding rows
Norman,
It is an interesting paradox, but strictly speaking the challenge was to make SpecialCells fail with =8192 blank cells. I think there were 16384 blank cells in the range where SpecialCells failed. Anyway, do you know why this happens? Thanks and regards, KL "Norman Jones" wrote in message ... Hi KL, A significant typo: Set rng = Range("A1:A16384") should read: Set rng = Range("A1:B16384") --- Regards, Norman |
#20
|
|||
|
|||
Macro for hiding rows
Hi Kl,
It is an interesting paradox, but strictly speaking the challenge was to make SpecialCells fail with =8192 blank cells. I think From the context of the thread and all preceding references, I took this to mean non-contiguous cells! g As for the paradox, I noted in the past that it is possible to provoke the solid range syndrome at points below 8192 non-contiguous areas. See, for example: http://tinyurl.com/8zvnd. --- Regards, Norman "KL" wrote in message ... Norman, It is an interesting paradox, but strictly speaking the challenge was to make SpecialCells fail with =8192 blank cells. I think there were 16384 blank cells in the range where SpecialCells failed. Anyway, do you know why this happens? Thanks and regards, KL "Norman Jones" wrote in message ... Hi KL, A significant typo: Set rng = Range("A1:A16384") should read: Set rng = Range("A1:B16384") --- Regards, Norman |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Macro Help In Excel | welshlad | General Discussion | 14 | October 26th, 2005 02:34 PM |
Closing File Error | jcliquidtension | General Discussion | 4 | October 20th, 2005 12:22 PM |
macro to add rows and copy and paste | Steve | Worksheet Functions | 9 | June 4th, 2004 06:14 PM |
macro to add rows and copy data into the rows | Steve | Worksheet Functions | 2 | June 3rd, 2004 08:50 PM |
Counting Rows in Excel Macro | Randy Wiseman | Worksheet Functions | 1 | December 14th, 2003 02:13 PM |