OK, here's an approach:
The concept is this:
you only have to identify ONE cell in the row, then use the
.EntireRow.Select
method.
You recognized that you could use:
Range(1:1,3:3,5:5)
but you could also use:
Range("A1,A3,A5").EntireRow.Select
So, the trick here is to somehow create an array of row numbers.
You could create a variable and use ReDim to change the size of the array to
accomodate new row numbers.
Or you could create a string variable and split it, like:
trows = "1,3,5,7,9,11"
rArray = Split(tRows, ",")
Then, use:
Range("A" & Join(rArray, ", A")).EntireRow.Select
I created the following macro, but it seems to have a problem with an array
size greater than 52:
Sub SelRows()
Dim rArray
Dim tRows As String
Dim R As Integer, nRows As Integer
nRows =
Application.WorksheetFunction.CountA(ActiveWorkbook.Sheets(1).Range("A1:A65000"))
' If (nRows > 104) Then nRows = 104
tRows = ""
Range("A1").Select
For R = 1 To nRows Step 2
If (Len(tRows) > 0) Then tRows = tRows & ","
tRows = tRows & R
Next R
rArray = Split(tRows, ",")
tRows = "A" & Join(rArray, ",A")
Range(tRows).EntireRow.Select
End Sub
Paul
-----------------------------------------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-----------------------------------------
From: Mandeep Baluja <rockerna...@gmail.com>
>To: excel-macros@googlegroups.com
>Sent: Tuesday, November 25, 2014 2:20 AM
>Subject: $$Excel-Macros$$ Re: How to select a range in vba ?
>
>
>
>bosss, you're not getting me i know that code is fine what I want is to run a
>loop for my query select every alternate rows. why should i write
>1:1,3:3,5:5................... ..............................
>............................ 10001:10001. Basically i can certainly understand
>to do an operation with alternate rows using a loop but here the point is for
>selection only.
>
>
>when ever i run a loop like
>
>
>dim i as integer.
>rows(i).entirerow.select
>next
>it will move to next row why it's is not keeping the previous rows selected.
>Every one can select A1:A1000 with a loop. what my query is for alternate
>rows.
>
>On Tuesday, November 25, 2014 11:30:40 AM UTC+5:30, Mandeep Baluja wrote:
>
>>
>> Sub Exercise()
>>
>> Range("1:1,3:3, 5:5, 7:7").Select
>>
>>End Sub
>>
>>
>>See this how this rows are selected I want automation for this with the help
>>of loop .
>>
>>
>>
>>On Tuesday, November 25, 2014 11:21:46 AM UTC+5:30, Mandeep Baluja wrote:
>>Sorry not working try this this also selecting the whole range.
>>>
>>>On Monday, November 24, 2014 5:05:14 PM UTC+5:30, Mandeep Baluja wrote:
>>>Dear All,
>>>>
>>>>
>>>>I hope you have a correct idea for my question I am asking to select rows
>>>>in data with the help of loop. Please note that what i want is to run a
>>>>loop select a row move next and keep the previous row selected too. I need
>>>>a efficient code for this.
>>>>
>>>>
>>>>
>>>>
>>>>Regards,
>>>>Mandeep Baluja.
>>>>
>>>>
>--
>Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
>=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>https://www.facebook.com/discussexcel
>
>FORUM RULES
>
>1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
>Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
>quick attention or may not be answered.
>2) Don't post a question in the thread of another member.
>3) Don't post questions regarding breaking or bypassing any security measure.
>4) Acknowledge the responses you receive, good or bad.
>5) Jobs posting is not allowed.
>6) Sharing copyrighted material and their links is not allowed.
>
>NOTE : Don't ever post confidential data in a workbook. Forum owners and
>members are not responsible for any loss.
>---
>You received this message because you are subscribed to the Google Groups "MS
>EXCEL AND VBA MACROS" group.
>To unsubscribe from this group and stop receiving emails from it, send an
>email to excel-macros+unsubscr...@googlegroups.com.
>To post to this group, send email to excel-macros@googlegroups.com.
>Visit this group at http://groups.google.com/group/excel-macros.
>For more options, visit https://groups.google.com/d/optout.
>
>
>
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
