error 1004

  • Thread starter Thread starter Macin
  • Start date Start date
M

Macin

Welcome Everyone,

I have a problem with the following code.

I want to select multiple rows in a very huge spreadsheet. But, when I
use this code, I receive an error: Runtime error 1004, Method 'Range'
of Object '_global' failed. What is wrong? Or maybe there is other
better way to select autmatically empty rows in a spreadsheet?

Thanks for help,
Martin

Public Sub delhol()
'
' delhol Macro
'
' Keyboard Shortcut: Ctrl+d
'

Dim k As Variant
Dim rr As String

k = Array(34, 35, 38, 39, 40, 77, 133, 182, 207, 209, 225, 226, 295,
299, 300, 338, 394, 437, 468, 470, 480, 481, 560, _
591, 599, 655, 712, 729, 746, 755, 756, 852, 860, 962, 990, 1005,
1006, 1077, 1081, 1082, 1083, 1114, 1176, _
1217, 1252, 1260, 1261, 1338, 1342, 1343, 1344, 1375, 1381, 1437,
1492, 1511, 1513, 1535, 1536, 1599, 1603, 1604, 1605, _
1642, 1698, 1742, 1772, 1774, 1785, 1786, 1860, 1864, 1865, 1903,
1959, 1997, 2033, 2034, 2035, 2040, 2041, _
2121, 2122, 2126, 2156, 2157, 2165, 2271, 2272, 2315, 2316, 2381,
2386, 2387, 2418, 2526, 2527, 2556, 2570, 2571, _
2576)

rr = "A" & k(0) & ":EO" & k(0)
For i = 1 To 25 'UBound(k) - 1
rr = rr & ", A" & k(i) & ":EO" & k(i)
Next

Range(rr).Select
 
For i = 1 To 25 'UBound(k) - 1

Obviously, this line s for testing only, and 25 was maximum value I
could use to not get the error.
Martin
 
I'm guessing that the string is just too long.

Public Sub delhol()
'
' delhol Macro
'
' Keyboard Shortcut: Ctrl+d
'

Dim k As Variant
Dim rr As range

k = Array(34, 35, 38, 39, 40, 77, 133, 182, 207, 209, 225, 226, 295, _
299, 300, 338, 394, 437, 468, 470, 480, 481, 560, _
591, 599, 655, 712, 729, 746, 755, 756, 852, 860, 962, 990, 1005, _
1006, 1077, 1081, 1082, 1083, 1114, 1176, _
1217, 1252, 1260, 1261, 1338, 1342, 1343, 1344, 1375, 1381, 1437, _
1492, 1511, 1513, 1535, 1536, 1599, 1603, 1604, 1605, _
1642, 1698, 1742, 1772, 1774, 1785, 1786, 1860, 1864, 1865, 1903, _
1959, 1997, 2033, 2034, 2035, 2040, 2041, _
2121, 2122, 2126, 2156, 2157, 2165, 2271, 2272, 2315, 2316, 2381, _
2386, 2387, 2418, 2526, 2527, 2556, 2570, 2571, _
2576)

set rr = activesheet.range("A" & k(0) & ":EO" & k(0))
For i = 1 To UBound(k) - 1
set rr = union(rr, activesheet.range(", A" & k(i) & ":EO" & k(i))
Next i

rr.Select

End sub

(Untested. Uncompiled.)
 
I could only get 21 iterations to work without getting the error message.
Must be a limitation on the number of characters that can be stored in a
discontiguous range.
 
Hi Dave.

Tested. Fine, but change:
set rr = union(rr, activesheet.range(", A" & k(i) & ":EO" & k(i))
Click to expand...
with
Set rr = Union(rr, ActiveSheet.Range("A" & k(i) & ":EO" & k(i)))
Regards
Eliano
 
Sorry Dave.
I believe is better to change:
For i = 1 To UBound(k) - 1
set rr = union(rr, activesheet.range(", A" & k(i) & ":EO" & k(i))
in:
For i = LBound(k) To UBound(k)
Set rr = Union(rr, ActiveSheet.Range("A" & k(i) & ":EO" & k(i)))

Regards
Eliano
 
Yep.

I just copied and pasted. I should have looked more closely.

Glad you fixed it.
 
Are you keeping the first set statement:

set rr = activesheet.range("A" & k(0) & ":EO" & k(0))

If you are, then you're including that range twice.

If you are not, then this won't work. The union of something with nothing (rr
is nothing to start) will fail.

You could use:

Public Sub delhol()
'
' delhol Macro
'
' Keyboard Shortcut: Ctrl+d
'

Dim k As Variant
Dim rr As range

k = Array(34, 35, 38, 39, 40, 77, 133, 182, 207, 209, 225, 226, 295, _
299, 300, 338, 394, 437, 468, 470, 480, 481, 560, _
591, 599, 655, 712, 729, 746, 755, 756, 852, 860, 962, 990, 1005, _
1006, 1077, 1081, 1082, 1083, 1114, 1176, _
1217, 1252, 1260, 1261, 1338, 1342, 1343, 1344, 1375, 1381, 1437, _
1492, 1511, 1513, 1535, 1536, 1599, 1603, 1604, 1605, _
1642, 1698, 1742, 1772, 1774, 1785, 1786, 1860, 1864, 1865, 1903, _
1959, 1997, 2033, 2034, 2035, 2040, 2041, _
2121, 2122, 2126, 2156, 2157, 2165, 2271, 2272, 2315, 2316, 2381, _
2386, 2387, 2418, 2526, 2527, 2556, 2570, 2571, _
2576)

set rr = nothing
For i = lbound(k) To UBound(k)
if rr is nothing then
set rr = activesheet.range("A" & k(i) & ":eo" & k(i))
else
set rr = union(rr, activesheet.range("A" & k(i) & ":EO" & k(i))
end if
next i

rr.Select

End sub

(Still untested!)
 
Hi Dave.
Also the first version is fine for me.
However, for the last version:
----
Dim i As Long
----
Set rr = Union(rr, ActiveSheet.Range("A" & k(i) & ":EO" & k(i)))
'<---- one ")" more for Union close.

Regards
Eliano
 
Back
Top