select rows

[luceze]

Does anyone know how to selcect non adjacent rows depending what is in the first cell of that row.

For example:

if a1=0 then select the entire row
then if a2 also equals 0 then select that row also.
then if a3 does not equal 0 don't select that row, etc.

What I was trying to do was create a string with all the row addresses separated by a comma but it won't let me use the string as a range.

Here's what I have so far.

Dim cell
Dim strCollect As String
stringcollect = Empty
For Each cell In Range("a1:a1000&quot
cell.Activate
If ActiveCell = 1 Then
If strCollect = Empty Then
strCollect = ActiveCell.EntireRow.Address
Else
strCollect = strCollect & "," & ActiveCell.EntireRow.Address
End If
End If
Next
Range(strCollect).Activate

Any ideas would be appreciated.

 

[Loomah]

Hi
I don't know if it's me but your code works fine for me. The only thing I'd suggest is declaring "cell" (or just call it c) properly as range then not bother to activate any cells and substitute "activecell" with "cell" (or c).

The only other thing is that in your description you say you want to test for 0 but in your code you have "if activecell = 1" A simple typo??

Other than that, as I say, I can't see any problems and infact quite like the code!

;-)

If a man says something and there are no women there to hear him, is he still wrong?

 

[luceze]

Thanks for the reply. I think I know what is wrong now. Excel seems only to be able to accept 34 rows as a range. If I have more then 34 ones in the range then it returns an error. If I have 34 or less it works fine. I ended up using auto filter then selecting all the data which seems to work fine.

 

[Loomah]

Hi
I'm glad you found a solution. The problem you were having (I managed to recreate it with the 34 in mind) has, I think, more to do with the length of the string than the number of rows that can be selected.

Using

Address(False, False)

reduces the length of the string being added each time (removes the $s) but it still runs out of ability at around 44-45 rows.

Such is life!

;-)

If a man says something and there are no women there to hear him, is he still wrong?