Run-time error '1004':

[luzikedy]

Hello All,

I have a problem with a macro below as it returns Run-time error '1004': Method 'Range' of object '_Global failed.

I have identical macro where if cell.value 2,3,4,5 etc. works perfectly but not the one below.

Worksheet is not locked and scroll area is not limited in any way as well as all rows and columns are set to unhide.

Values in column "AU" come from the same formula with the same formatting etc.

What can be changed in the macro is that it doesn't have to select whole row it can be let's say from A to Z

and from row 6 to 6000.

I also noticed that if I have value 1 in rows 10-100 it works ok, but when 1 is in row 101 it stops working and is not selecting rows from 10 to 101. Analogical situation happens in other rows.

Any ideas why this doesn't work?


Sub Select_Row1 ()


Dim cell As Range
Dim myrows As String
For Each cell In Range("au6:au" & [A65536].End(xlUp).Row)
If cell.Value = 1 Then
myrows = myrows & cell.EntireRow.Address & ","
End If
Next cell
myrows = Left(myrows, Len(myrows) - 1)
Range(myrows).Select


End Sub

 

[FormR]

Any ideas why this doesn't work?

The length of the string parameter has an upper limit that you are probably hitting.

What can be changed in the macro is that it doesn't have to select whole row it can be let's say from A to Z

You could try something like this:

Sub Select_Row1()
Dim cell As Range, myrows As Range
For Each cell In Range("au6:au" & [A65536].End(xlUp).Row)
    If cell.Value = 1 Then
        If Not myrows Is Nothing Then
            Set myrows = Union(myrows, Range(Cells(cell.Row, "A"), Cells(cell.Row, "Z")))
                Else
            Set myrows = Range(Cells(cell.Row, "A"), Cells(cell.Row, "Z"))
        End If
    End If
Next cell
If Not myrows Is Nothing Then myrows.Select
End Sub

 

[luzikedy]

Works perfect, thank you!