select multiple rows using variable

Question

I have a loop that runs through the worksheet and keeps track of works where the guy is named "bill" then memorizes those rows and i want to copy them over to the next sheet.

my problem is that 'works 'Range("525:525,504:504,502:502,497:497,496:496,495:495,494:494,493:493,492:492,491:491,478:478,471:471,453:453,450:450,449:449,448:448,447:447,446:446,445:445,444:444,443:443,442:442,441:441,440:440,439:439,438:438,437:437,436:436,427:427,129:129").Select

but when i use a variable to create that list of rows, it doesnt select the rows

Dim accountsArray(2, 2) As String

accountsArray(1, 0) = "test"
accountsArray(1, 1) = "bill"

Dim rngCounter As String
rngCounter = Chr(34)
For i = ActiveSheet.Cells(ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row, 1).Row To 2          Step -1

    'save rows with bill
    If InStr((ActiveSheet.Cells(i, 2).Text), accountsArray(1, 1)) <> 0 Then
        If Len(rngCounter) < 2 Then rngCounter = rngCounter & i & ":" & i Else rngCounter = rngCounter & "," & i & ":" & i
    End If
Next i

rngCounter = rngCounter & Chr(34)

'works
'Range("525:525,504:504,502:502,497:497,496:496,495:495,494:494,493:493,492:492,491:491,478:478,471:471,453:453,450:450,449:449,448:448,447:447,446:446,445:445,444:444,443:443,442:442,441:441,440:440,439:439,438:438,437:437,436:436,427:427,129:129").Select

'does not work
Range(rngCounter).Select

Selection.Copy
Sheets.Add after:=ActiveSheet
ActiveSheet.Paste

Enigmativity · Accepted Answer · 2014-05-15 00:24:44Z

2

The way the code currently is written you're effectively calling this:

Range("""525:525,504:504,502:502,497:497,496:496,495:495,494:494,493:493,492:492,491:491,478:478,471:471,453:453,450:450,449:449,448:448,447:447,446:446,445:445,444:444,443:443,442:442,441:441,440:440,439:439,438:438,437:437,436:436,427:427,129:129""").Select

So take out the Chr(34) (double quotes) that you are adding to the beginning and end of your string and it'll work fine.

answered May 15, 2014 at 0:24

Enigmativity

117k12 gold badges101 silver badges184 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

select multiple rows using variable

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related