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

"Macin" wrote:

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

Macin wrote:

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

--

Dave Peterson

Hi Dave.

Tested. Fine, but change:
set rr = union(rr, activesheet.range(", A" & k(i) & ":EO" & k(i))
with
Set rr = Union(rr, ActiveSheet.Range("A" & k(i) & ":EO" & k(i)))
Regards
Eliano


On 16 Feb, 20:55, Dave Peterson wrote:
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.)





Macin wrote:

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

--

Dave Peterson- Nascondi testo tra virgolette -

- Mostra testo tra virgolette -

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


On 17 Feb, 02:10, eliano wrote:
Hi Dave.

Tested. Fine, but change: * *set rr = union(rr, activesheet.range(", A" & k(i) & ":EO" & k(i))

with
Set rr = Union(rr, ActiveSheet.Range("A" & k(i) & ":EO" & k(i)))
Regards
Eliano

On 16 Feb, 20:55, Dave Peterson wrote:



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

Macin wrote:

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

--

Dave Peterson- Nascondi testo tra virgolette -

- Mostra testo tra virgolette -- Nascondi testo tra virgolette -

- Mostra testo tra virgolette -

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!)

eliano wrote:

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

On 17 Feb, 02:10, eliano wrote:
Hi Dave.

Tested. Fine, but change: set rr = union(rr, activesheet.range(", A" & k(i) & ":EO" & k(i))

with
Set rr = Union(rr, ActiveSheet.Range("A" & k(i) & ":EO" & k(i)))
Regards
Eliano

On 16 Feb, 20:55, Dave Peterson wrote:



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

Macin wrote:

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

--

Dave Peterson- Nascondi testo tra virgolette -

- Mostra testo tra virgolette -- Nascondi testo tra virgolette -

- Mostra testo tra virgolette -

--

Dave Peterson

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



On 17 Feb, 03:15, Dave Peterson wrote:
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!)





elianowrote:

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

On 17 Feb, wrote:
Hi Dave.

Tested. Fine, but change: * *set rr = union(rr, activesheet.range(", A" & k(i) & ":EO" & k(i))

with
Set rr = Union(rr, ActiveSheet.Range("A" & k(i) & ":EO" & k(i)))
Regards
Eliano

On 16 Feb, 20:55, Dave Peterson wrote:

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

Macin wrote:

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

--

Dave Peterson- Nascondi testo tra virgolette -

- Mostra testo tra virgolette -- Nascondi testo tra virgolette -

- Mostra testo tra virgolette -

--

Dave Peterson- Nascondi testo tra virgolette -

- Mostra testo tra virgolette -

Yep.

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

Glad you fixed it.

eliano wrote:

Hi Dave.

Tested. Fine, but change:
set rr = union(rr, activesheet.range(", A" & k(i) & ":EO" & k(i))
with
Set rr = Union(rr, ActiveSheet.Range("A" & k(i) & ":EO" & k(i)))
Regards
Eliano

On 16 Feb, 20:55, Dave Peterson wrote:
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.)





Macin wrote:

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

--

Dave Peterson- Nascondi testo tra virgolette -

- Mostra testo tra virgolette -

--

Dave Peterson