Macro for hiding rows

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?

I think, that no macro is necessary to to this job! Simply apply an
Autofilter and choose Not empty when clicking the drop-down arrow!

Regards,
Stefi


„Luke” ezt �*rta:

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

Luke,

try this VBA code: (this code consider the name of the sheet as Sheet1 and
you want to hide the rows that the cell in the column A is empty)

Sub HideEmptyRows()

With Worksheets("Sheet1")

lastrow = .Range("A65536").End(xlUp).Row

For i = 1 To lastrow

If .Range("A" & i).Value = "" Then

Rows(i & ":" & i).EntireRow.Hidden = True

End If

Next i

End With

End Sub

i hope this can help you!

Have a nice Christmas end a great New Year!

Pedro

"Luke" wrote:

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

another way without macros:

1) select one of the columns where empty cells correspond to empty rows
2) menu Edit>Goto...
3) press the button 'Special...'
4) mark the 'Blanks' option and press 'OK'
5) menu Format>Rows>Hide...

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" <[email protected]> wrote in message news:[email protected]...
>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?

Hi Luke,

As an alternative, try:

Try:

'=============>>
Public Sub Tester()

On Error Resume Next
Columns(2).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0

End Sub
'<<=============


---
Regards,
Norman


"Luke" <[email protected]> wrote in message
news:[email protected]...
>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?

it is looking easy to hide blank cell rows rather containing zero value
while i exercise on your point you may try the following for hiding the
blank cell rows
Ctrl+G (Go To Command) or Edit I Go To I
Select Special
Select Blank
Enter or Click OK
The above process/commands selects all bank cells. Then
Format I Row I Hide


--
gandhi318Posted from - http://www.officehelp.in

Hi Luke:

> Columns(2).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Was intended to read::

Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

---
Regards,
Norman


"Norman Jones" <[email protected]> wrote in message
news:[email protected]...
> Hi Luke,
>
> As an alternative, try:
>
> Try:
>
> '=============>>
> Public Sub Tester()
>
> On Error Resume Next
> Columns(2).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
> On Error GoTo 0
>
> End Sub
> '<<=============
>
>
> ---
> Regards,
> Norman
>
>
> "Luke" <[email protected]> wrote in message
> news:[email protected]...
>>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?
>
>

just to add two comments:

1) I guess the task is to hide not to delete, so probably:
Columns(2).SpecialCells(xlCellTypeBlanks).EntireRow.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" <[email protected]> wrote in message news:[email protected]...
> Hi Luke,
>
> As an alternative, try:
>
> Try:
>
> '=============>>
> Public Sub Tester()
>
> On Error Resume Next
> Columns(2).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
> On Error GoTo 0
>
> End Sub
> '<<=============
>
>
> ---
> Regards,
> Norman
>
>
> "Luke" <[email protected]> wrote in message news:[email protected]...
>>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?
>
>

Sir
your suggested micro is not working
please check yourself and see a revised one is suggested
you may see my reply which hides emptry cell rows









PedroPastre Wrote:
> Luke,
>
> try this VBA code: (this code consider the name of the sheet as Sheet1
> and
> you want to hide the rows that the cell in the column A is empty)
>
> Sub HideEmptyRows()
>
> With Worksheets("Sheet1")
>
> lastrow = .Range("A65536").End(xlUp).Row
>
> For i = 1 To lastrow
>
> If .Range("A" & i).Value = "" Then
>
> Rows(i & ":" & i).EntireRow.Hidden = True
>
> End If
>
> Next i
>
> End With
>
> End Sub
>
> i hope this can help you!
>
> Have a nice Christmas end a great New Year!
>
> Pedro
>
> "Luke" wrote:
>
> > 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?


--
gandhi318Posted from - http://www.officehelp.in

Hi,

> your suggested micro is not working

What do you mean it "is not working"? Can you please be more specific? It does to me, only it is slow, blinking and may need some
optimization. Perhaps:

Sub HideEmptyRows()
Application.ScreenUpdating=False
With Worksheets("Sheet1")
lastrow = .Range("A65536").End(xlUp).Row
For i = 1 To lastrow
If .Range("A" & i).Value = "" Then
Rows(i ).EntireRow.Hidden = True
End If
Next i
End With
Application.ScreenUpdating=True
End Sub

Also if there is a significant number of rows I would use the SpecialCells one (see the the reply by Norman Jones) as it is way
faster.

> please check yourself and see a revised one is suggested

You can guess...;-)

Regards,
KL

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).EntireRow.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" <[email protected]> wrote in message
news:%[email protected]...
> just to add two comments:
>
> 1) I guess the task is to hide not to delete, so probably:
> Columns(2).SpecialCells(xlCellTypeBlanks).EntireRow.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" <[email protected]> wrote in message
> news:[email protected]...
>> Hi Luke,
>>
>> As an alternative, try:
>>
>> Try:
>>
>> '=============>>
>> Public Sub Tester()
>>
>> On Error Resume Next
>> Columns(2).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
>> On Error GoTo 0
>>
>> End Sub
>> '<<=============
>>
>>
>> ---
>> Regards,
>> Norman
>>
>>
>> "Luke" <[email protected]> wrote in message
>> news:[email protected]...
>>>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?
>>
>>
>

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" <[email protected]> wrote in message
news:%[email protected]...
> another way without macros:
>
> 1) select one of the columns where empty cells correspond to empty rows
> 2) menu Edit>Goto... 3) press the button 'Special...'
> 4) mark the 'Blanks' option and press 'OK'
> 5) menu Format>Rows>Hide...
>
> 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" <[email protected]> wrote in message
> news:[email protected]...
>>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?

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" <[email protected]> wrote in message
news:oVSqf.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" <[email protected]> wrote in message
> news:%[email protected]...
>> another way without macros:
>>
>> 1) select one of the columns where empty cells correspond to empty rows
>> 2) menu Edit>Goto... 3) press the button 'Special...'
>> 4) mark the 'Blanks' option and press 'OK'
>> 5) menu Format>Rows>Hide...
>>
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>>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?
>
>

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message news:[email protected]...
>> 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
>>
>
>

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" <[email protected]> wrote in message
news:u12UM%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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
>>>
>>
>>
>

Hi KL,

A significant typo:

> Set rng = Range("A1:A16384")

should read:

Set rng = Range("A1:B16384")

---
Regards,
Norman

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" <[email protected]> wrote in message news:[email protected]...
> Hi KL,
>
> A significant typo:
>
>> Set rng = Range("A1:A16384")
>
> should read:
>
> Set rng = Range("A1:B16384")
>
> ---
> Regards,
> Norman
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> Hi KL,
>>
>> A significant typo:
>>
>>> Set rng = Range("A1:A16384")
>>
>> should read:
>>
>> Set rng = Range("A1:B16384")
>>
>> ---
>> Regards,
>> Norman
>>
>

Hi Norman,

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

How bizarre! Have you made any progress in further research? Any hypothesis on why this happens? Also, I just thought your sample
code might run slightly faster if you avoid the looping in setting up the data:

'=============>>
Public Sub Demo2()
Dim x As Long, y As Long

Application.ScreenUpdating = False
Range("A2:B2").Value = "XYZ"
Range("A1:B2").AutoFill Range("A1:B16384")

x = Range("A1:B16384") _
.SpecialCells(xlCellTypeBlanks).Areas.Count
y = Range("A1:A16384"). _
SpecialCells(xlCellTypeBlanks).Areas.Count
Application.ScreenUpdating = True

MsgBox "Rng1 Areas =" & x _
& vbNewLine & _
"Rng2 Areas =" & y
End Sub
'<<=============

Thanks and regards,
KL

Hi KL,

> How bizarre! Have you made any progress in further research? Any
> hypothesis on why this happens?

In 'serious' code, I invariably use the segmenting ideas that emanated from
the quoted thread, so subsequent consideration has been relegated towards
the lower end of my (lengthy) 'must investigate' list. However, if I get
bored over the festive period, I will endeavour to find a reproducible
pattern.

>Also, I just thought your sample code might run slightly faster if you
>avoid the looping in setting up the data:

Undoubtedly, but very little thought went into the demo!

Have a happy holiday!

---
Regards,
Norman


"KL" <[email protected]> wrote in message
news:%[email protected]...
> Hi Norman,
>
>> 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.
>
> How bizarre! Have you made any progress in further research? Any
> hypothesis on why this happens? Also, I just thought your sample code
> might run slightly faster if you avoid the looping in setting up the data:
>
> '=============>>
> Public Sub Demo2()
> Dim x As Long, y As Long
>
> Application.ScreenUpdating = False
> Range("A2:B2").Value = "XYZ"
> Range("A1:B2").AutoFill Range("A1:B16384")
>
> x = Range("A1:B16384") _
> .SpecialCells(xlCellTypeBlanks).Areas.Count
> y = Range("A1:A16384"). _
> SpecialCells(xlCellTypeBlanks).Areas.Count
> Application.ScreenUpdating = True
>
> MsgBox "Rng1 Areas =" & x _
> & vbNewLine & _
> "Rng2 Areas =" & y
> End Sub
> '<<=============
>
> Thanks and regards,
> KL

> Have a happy holiday!

You too!
KL