Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
limit on [h]:mm time format? | Excel Discussion (Misc queries) | |||
Limit to number of named ranges before Excel starts messing things up? | Excel Worksheet Functions | |||
macro to time limit workbook | Excel Discussion (Misc queries) | |||
How can i work a formula for time limit? | Excel Worksheet Functions | |||
set time limit before closing sheet | Excel Programming |