Help! Limit on number of Ranges you can use at one time?
 

Hello -

I have the following code:

Dim numBlanks As Integer
numBlanks = 0
'For Each c In Range("D8:D240")
For Each c In Range("D8:D16, D17:D22, D23:D27, D29:D31, D33:D42, D44:D50,
D52:D57, D59:D78, D80:D83, D85:D86," & _
"D88:D94, D102:D105, D107:D111, D113:D115, D123:D128, D135:D136,
D143:D144, D146, D154:D156," & _
"D158:D159, D167:D170, D172:D174, D176, D178:D179, D187:D189, D191:D197,
D212")

'The following line is commented out
', D214:D218, D221:D230, D237:D240")

If c.Value = "" Then
numBlanks = numBlanks + 1
End If
Next c

MsgBox "The number of blank rows is: " & numBlanks

Note I have commented out the code for:
', D214:D218, D221:D230, D237:D240")

This runs fine until I try to put one more range in from the commented line;
e.g. D214:D218 (or either of the other two for that matter). I obviously
need to include these three ranges.

I am getting the following error:
Run-time error '1004'
Method 'Range' or object '_Worksheet' failed.
When I click on the help button of the error, it comes up totally blank.

Is there some limit on the amount of ranges you can use at one time? If
that's the case, is there some kind of workaround? If not, does anyone know
what is causing this?

Any help will be greatly appreciated!
--
Sandy

Help! Limit on number of Ranges you can use at one time?
 

The line of code is getting too long. Try using the union function to Union a
couple of range objects together...

dim rng1 as range
dim rng2 as range
dim rngAll as range

set rng1 = Range(...)
set rng2 = Range(...)
set rngAll = union(rng1, rng2)
--
HTH...

Jim Thomlinson


"Sandy" wrote:

Hello -

I have the following code:

Dim numBlanks As Integer
numBlanks = 0
'For Each c In Range("D8:D240")
For Each c In Range("D8:D16, D17:D22, D23:D27, D29:D31, D33:D42, D44:D50,
D52:D57, D59:D78, D80:D83, D85:D86," & _
"D88:D94, D102:D105, D107:D111, D113:D115, D123:D128, D135:D136,
D143:D144, D146, D154:D156," & _
"D158:D159, D167:D170, D172:D174, D176, D178:D179, D187:D189, D191:D197,
D212")

'The following line is commented out
', D214:D218, D221:D230, D237:D240")

If c.Value = "" Then
numBlanks = numBlanks + 1
End If
Next c

MsgBox "The number of blank rows is: " & numBlanks

Note I have commented out the code for:
', D214:D218, D221:D230, D237:D240")

This runs fine until I try to put one more range in from the commented line;
e.g. D214:D218 (or either of the other two for that matter). I obviously
need to include these three ranges.

I am getting the following error:
Run-time error '1004'
Method 'Range' or object '_Worksheet' failed.
When I click on the help button of the error, it comes up totally blank.

Is there some limit on the amount of ranges you can use at one time? If
that's the case, is there some kind of workaround? If not, does anyone know
what is causing this?

Any help will be greatly appreciated!
--
Sandy

Help! Limit on number of Ranges you can use at one time?
 

Hi Jim -

Thanks so much! That worked beautifully!
--
Sandy


"Jim Thomlinson" wrote:

The line of code is getting too long. Try using the union function to Union a
couple of range objects together...

dim rng1 as range
dim rng2 as range
dim rngAll as range

set rng1 = Range(...)
set rng2 = Range(...)
set rngAll = union(rng1, rng2)
--
HTH...

Jim Thomlinson


"Sandy" wrote:

Hello -

I have the following code:

Dim numBlanks As Integer
numBlanks = 0
'For Each c In Range("D8:D240")
For Each c In Range("D8:D16, D17:D22, D23:D27, D29:D31, D33:D42, D44:D50,
D52:D57, D59:D78, D80:D83, D85:D86," & _
"D88:D94, D102:D105, D107:D111, D113:D115, D123:D128, D135:D136,
D143:D144, D146, D154:D156," & _
"D158:D159, D167:D170, D172:D174, D176, D178:D179, D187:D189, D191:D197,
D212")

'The following line is commented out
', D214:D218, D221:D230, D237:D240")

If c.Value = "" Then
numBlanks = numBlanks + 1
End If
Next c

MsgBox "The number of blank rows is: " & numBlanks

Note I have commented out the code for:
', D214:D218, D221:D230, D237:D240")

This runs fine until I try to put one more range in from the commented line;
e.g. D214:D218 (or either of the other two for that matter). I obviously
need to include these three ranges.

I am getting the following error:
Run-time error '1004'
Method 'Range' or object '_Worksheet' failed.
When I click on the help button of the error, it comes up totally blank.

Is there some limit on the amount of ranges you can use at one time? If
that's the case, is there some kind of workaround? If not, does anyone know
what is causing this?

Any help will be greatly appreciated!
--
Sandy

Help! Limit on number of Ranges you can use at one time?
 

did you try using the COUNTA function. If yuo know that there are 3 empty
cells(say) that you don't care about then

Sub xxx()
Dim source As Range
Dim iEmpty As Long

Set source = Range("D8:D240")
iEmpty = 3

MsgBox "Empty Cell count = " & _
(source.Cells.Count - Application.WorksheetFunction.CountA(source)) -
iEmpty


End Sub


"Sandy" wrote:

Hi Jim -

Thanks so much! That worked beautifully!
--
Sandy


"Jim Thomlinson" wrote:

The line of code is getting too long. Try using the union function to Union a
couple of range objects together...

dim rng1 as range
dim rng2 as range
dim rngAll as range

set rng1 = Range(...)
set rng2 = Range(...)
set rngAll = union(rng1, rng2)
--
HTH...

Jim Thomlinson


"Sandy" wrote:

Hello -

I have the following code:

Dim numBlanks As Integer
numBlanks = 0
'For Each c In Range("D8:D240")
For Each c In Range("D8:D16, D17:D22, D23:D27, D29:D31, D33:D42, D44:D50,
D52:D57, D59:D78, D80:D83, D85:D86," & _
"D88:D94, D102:D105, D107:D111, D113:D115, D123:D128, D135:D136,
D143:D144, D146, D154:D156," & _
"D158:D159, D167:D170, D172:D174, D176, D178:D179, D187:D189, D191:D197,
D212")

'The following line is commented out
', D214:D218, D221:D230, D237:D240")

If c.Value = "" Then
numBlanks = numBlanks + 1
End If
Next c

MsgBox "The number of blank rows is: " & numBlanks

Note I have commented out the code for:
', D214:D218, D221:D230, D237:D240")

This runs fine until I try to put one more range in from the commented line;
e.g. D214:D218 (or either of the other two for that matter). I obviously
need to include these three ranges.

I am getting the following error:
Run-time error '1004'
Method 'Range' or object '_Worksheet' failed.
When I click on the help button of the error, it comes up totally blank.

Is there some limit on the amount of ranges you can use at one time? If
that's the case, is there some kind of workaround? If not, does anyone know
what is causing this?

Any help will be greatly appreciated!
--
Sandy