Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a limit to the number of items you can select?
I have the VBA code listed below.
I get an error Run-time error 1004 Method 'Range' of object '_Global' failed Basicly what I want to do is select all rows with Column C = Independent When I run it with lengthofselect = 79340 the error occurs on the Range(RowNumHolderNew).Select statment When I run it with lengthofselect = 16 it runs fine and selects the Rows I want it to. Is there a limit that you can only select so many rows at once in the Range() function? Do you have a simpler way of doing this? i = 1 Do While Range("C" & i) < "" If Range("C" & i).Value = "Independent" Then RowNumHolder = i & ":" & i & "," & RowNumHolder End If i = i + 1 Loop lengthofselect = Len(RowNumHolder) RowNumHolderNew = Mid(RowNumHolder, 1, lengthofselect - 1) 'removes last comma Range(RowNumHolderNew).Select |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a limit to the number of items you can select?
Declare your variable as type long, iso as type integer: Dim LengthofSelect
as Long -- Hth Kassie Kasselman Change xxx to hotmail "pokdbz" wrote: I have the VBA code listed below. I get an error Run-time error 1004 Method 'Range' of object '_Global' failed Basicly what I want to do is select all rows with Column C = Independent When I run it with lengthofselect = 79340 the error occurs on the Range(RowNumHolderNew).Select statment When I run it with lengthofselect = 16 it runs fine and selects the Rows I want it to. Is there a limit that you can only select so many rows at once in the Range() function? Do you have a simpler way of doing this? i = 1 Do While Range("C" & i) < "" If Range("C" & i).Value = "Independent" Then RowNumHolder = i & ":" & i & "," & RowNumHolder End If i = i + 1 Loop lengthofselect = Len(RowNumHolder) RowNumHolderNew = Mid(RowNumHolder, 1, lengthofselect - 1) 'removes last comma Range(RowNumHolderNew).Select |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a limit to the number of items you can select?
That didn't seem to work.
"Kassie" wrote: Declare your variable as type long, iso as type integer: Dim LengthofSelect as Long -- Hth Kassie Kasselman Change xxx to hotmail "pokdbz" wrote: I have the VBA code listed below. I get an error Run-time error 1004 Method 'Range' of object '_Global' failed Basicly what I want to do is select all rows with Column C = Independent When I run it with lengthofselect = 79340 the error occurs on the Range(RowNumHolderNew).Select statment When I run it with lengthofselect = 16 it runs fine and selects the Rows I want it to. Is there a limit that you can only select so many rows at once in the Range() function? Do you have a simpler way of doing this? i = 1 Do While Range("C" & i) < "" If Range("C" & i).Value = "Independent" Then RowNumHolder = i & ":" & i & "," & RowNumHolder End If i = i + 1 Loop lengthofselect = Len(RowNumHolder) RowNumHolderNew = Mid(RowNumHolder, 1, lengthofselect - 1) 'removes last comma Range(RowNumHolderNew).Select |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a limit to the number of items you can select?
Dim Rng as range
dim i as long i = 1 set rng = nothing with activesheet Do While trim(.Range("C" & i)) < "" If .Range("C" & i).Value = "Independent" Then if rng is nothing then set rng = .range("C" & i) else set rng = union(rng, .range("C" & i) end if end if i = i + 1 Loop if rng is nothing then msgbox "Nothing to select" else rng.entirerow.select end if ========= Personally, I like ..cells(i,"C") instead of ..range("C" & i) But both will work ok. pokdbz wrote: I have the VBA code listed below. I get an error Run-time error 1004 Method 'Range' of object '_Global' failed Basicly what I want to do is select all rows with Column C = Independent When I run it with lengthofselect = 79340 the error occurs on the Range(RowNumHolderNew).Select statment When I run it with lengthofselect = 16 it runs fine and selects the Rows I want it to. Is there a limit that you can only select so many rows at once in the Range() function? Do you have a simpler way of doing this? i = 1 Do While Range("C" & i) < "" If Range("C" & i).Value = "Independent" Then RowNumHolder = i & ":" & i & "," & RowNumHolder End If i = i + 1 Loop lengthofselect = Len(RowNumHolder) RowNumHolderNew = Mid(RowNumHolder, 1, lengthofselect - 1) 'removes last comma Range(RowNumHolderNew).Select -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a limit to the number of items you can select?
Oops. Add one more closing paren:
set rng = union(rng, .range("C" & i) becomes set rng = union(rng, .range("C" & i)) Dave Peterson wrote: Dim Rng as range dim i as long i = 1 set rng = nothing with activesheet Do While trim(.Range("C" & i)) < "" If .Range("C" & i).Value = "Independent" Then if rng is nothing then set rng = .range("C" & i) else set rng = union(rng, .range("C" & i) end if end if i = i + 1 Loop if rng is nothing then msgbox "Nothing to select" else rng.entirerow.select end if ========= Personally, I like .cells(i,"C") instead of .range("C" & i) But both will work ok. pokdbz wrote: I have the VBA code listed below. I get an error Run-time error 1004 Method 'Range' of object '_Global' failed Basicly what I want to do is select all rows with Column C = Independent When I run it with lengthofselect = 79340 the error occurs on the Range(RowNumHolderNew).Select statment When I run it with lengthofselect = 16 it runs fine and selects the Rows I want it to. Is there a limit that you can only select so many rows at once in the Range() function? Do you have a simpler way of doing this? i = 1 Do While Range("C" & i) < "" If Range("C" & i).Value = "Independent" Then RowNumHolder = i & ":" & i & "," & RowNumHolder End If i = i + 1 Loop lengthofselect = Len(RowNumHolder) RowNumHolderNew = Mid(RowNumHolder, 1, lengthofselect - 1) 'removes last comma Range(RowNumHolderNew).Select -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a limit to the number of items you can select?
Works great,
Could you explain it a bit using the Unions? "Dave Peterson" wrote: Oops. Add one more closing paren: set rng = union(rng, .range("C" & i) becomes set rng = union(rng, .range("C" & i)) Dave Peterson wrote: Dim Rng as range dim i as long i = 1 set rng = nothing with activesheet Do While trim(.Range("C" & i)) < "" If .Range("C" & i).Value = "Independent" Then if rng is nothing then set rng = .range("C" & i) else set rng = union(rng, .range("C" & i) end if end if i = i + 1 Loop if rng is nothing then msgbox "Nothing to select" else rng.entirerow.select end if ========= Personally, I like .cells(i,"C") instead of .range("C" & i) But both will work ok. pokdbz wrote: I have the VBA code listed below. I get an error Run-time error 1004 Method 'Range' of object '_Global' failed Basicly what I want to do is select all rows with Column C = Independent When I run it with lengthofselect = 79340 the error occurs on the Range(RowNumHolderNew).Select statment When I run it with lengthofselect = 16 it runs fine and selects the Rows I want it to. Is there a limit that you can only select so many rows at once in the Range() function? Do you have a simpler way of doing this? i = 1 Do While Range("C" & i) < "" If Range("C" & i).Value = "Independent" Then RowNumHolder = i & ":" & i & "," & RowNumHolder End If i = i + 1 Loop lengthofselect = Len(RowNumHolder) RowNumHolderNew = Mid(RowNumHolder, 1, lengthofselect - 1) 'removes last comma Range(RowNumHolderNew).Select -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a limit to the number of items you can select?
Union() with ranges is like using concatenate() with strings--or sum() with
numbers. It forms a giant(?) range of cells. try: with activesheet union(.range("a1"),.range("b3"),.range("c5")).sele ct end with Don't use this in real life, though: activesheet.range("a1,b3,c5").select would be easier to understand. pokdbz wrote: Works great, Could you explain it a bit using the Unions? "Dave Peterson" wrote: Oops. Add one more closing paren: set rng = union(rng, .range("C" & i) becomes set rng = union(rng, .range("C" & i)) Dave Peterson wrote: Dim Rng as range dim i as long i = 1 set rng = nothing with activesheet Do While trim(.Range("C" & i)) < "" If .Range("C" & i).Value = "Independent" Then if rng is nothing then set rng = .range("C" & i) else set rng = union(rng, .range("C" & i) end if end if i = i + 1 Loop if rng is nothing then msgbox "Nothing to select" else rng.entirerow.select end if ========= Personally, I like .cells(i,"C") instead of .range("C" & i) But both will work ok. pokdbz wrote: I have the VBA code listed below. I get an error Run-time error 1004 Method 'Range' of object '_Global' failed Basicly what I want to do is select all rows with Column C = Independent When I run it with lengthofselect = 79340 the error occurs on the Range(RowNumHolderNew).Select statment When I run it with lengthofselect = 16 it runs fine and selects the Rows I want it to. Is there a limit that you can only select so many rows at once in the Range() function? Do you have a simpler way of doing this? i = 1 Do While Range("C" & i) < "" If Range("C" & i).Value = "Independent" Then RowNumHolder = i & ":" & i & "," & RowNumHolder End If i = i + 1 Loop lengthofselect = Len(RowNumHolder) RowNumHolderNew = Mid(RowNumHolder, 1, lengthofselect - 1) 'removes last comma Range(RowNumHolderNew).Select -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation items limit | Excel Discussion (Misc queries) | |||
Finding maximum qty of items to reach given limit | Excel Worksheet Functions | |||
Is there a limit to the number of items you can use in lists?? | Excel Discussion (Misc queries) | |||
Listboxes and select items in it | Excel Programming | |||
Limit to the number of items in a cell | Excel Worksheet Functions |