SelectRegion Loop
 

I have a sheet with values similar to this:
148 407 118
118 10003 169 364 15
15 222 67
67 296 69
69 298 24
24 291 177


148 407 118
118 10003 169 10004 20 223 13
13 213 121
121 370 124
124 374 145


186 452 12
12 212 50
50 268 47
47 10018 74 10084 58
58 285 141
141 399 143

I wish to select each region or set of values i.e there are three in
the example given above before cuting the values and copying to a new
sheet. I need a macro that can loop through the sheet, find a value
(i.e. anything that isn't blank) within a cell to identify a new
region, select that region, cut to a new sheet, return to the original
sheet before continuing with again.
Thanks

SelectRegion Loop
 

Public Sub ProcessData()
Dim i As Long
Dim iLastRow As Long
Dim iStart As Long
Dim iNextRow As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
iStart = 1
iNextRow = 1
For i = 1 To iLastRow + 1
If .Cells(i, "A").Value = "" Then
.Cells(iStart, "A").Resize(i - iStart).EntireRow.Copy _
Worksheets("Sheet2").Range("A" & iNextRow)
iNextRow = iNextRow + i - iStart
Do While i <= iLastRow And .Cells(i + 1, "A").Value = ""
i = i + 1
Loop
iStart = i + 1
End If
Next i

End With

End Sub

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


wrote in message
ups.com...
I have a sheet with values similar to this:
148 407 118
118 10003 169 364 15
15 222 67
67 296 69
69 298 24
24 291 177


148 407 118
118 10003 169 10004 20 223 13
13 213 121
121 370 124
124 374 145


186 452 12
12 212 50
50 268 47
47 10018 74 10084 58
58 285 141
141 399 143

I wish to select each region or set of values i.e there are three in
the example given above before cuting the values and copying to a new
sheet. I need a macro that can loop through the sheet, find a value
(i.e. anything that isn't blank) within a cell to identify a new
region, select that region, cut to a new sheet, return to the original
sheet before continuing with again.
Thanks

SelectRegion Loop
 

Bob

Sorry this appears not to have done the trick.

I'll repeat again. I want to loop through my worksheet until I find a
set of values or constants i.e. populated cells. Once this is done I
want to select the whole region at which I'll activate another sub to
edit the cells before returning to the original sub and repeating

Thanks

Simon
Bob Phillips wrote:
Public Sub ProcessData()
Dim i As Long
Dim iLastRow As Long
Dim iStart As Long
Dim iNextRow As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
iStart = 1
iNextRow = 1
For i = 1 To iLastRow + 1
If .Cells(i, "A").Value = "" Then
.Cells(iStart, "A").Resize(i - iStart).EntireRow.Copy _
Worksheets("Sheet2").Range("A" & iNextRow)
iNextRow = iNextRow + i - iStart
Do While i <= iLastRow And .Cells(i + 1, "A").Value = ""
i = i + 1
Loop
iStart = i + 1
End If
Next i

End With

End Sub

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


wrote in message
ups.com...
I have a sheet with values similar to this:
148 407 118
118 10003 169 364 15
15 222 67
67 296 69
69 298 24
24 291 177


148 407 118
118 10003 169 10004 20 223 13
13 213 121
121 370 124
124 374 145


186 452 12
12 212 50
50 268 47
47 10018 74 10084 58
58 285 141
141 399 143

I wish to select each region or set of values i.e there are three in
the example given above before cuting the values and copying to a new
sheet. I need a macro that can loop through the sheet, find a value
(i.e. anything that isn't blank) within a cell to identify a new
region, select that region, cut to a new sheet, return to the original
sheet before continuing with again.
Thanks

SelectRegion Loop
 

You've changed the spec

Version 1.

find a value
(i.e. anything that isn't blank) within a cell to identify a new
region, select that region, cut to a new sheet, return to the original
sheet before continuing with again.

Version 2

find a
set of values or constants i.e. populated cells. Once this is done I
want to select the whole region at which I'll activate another sub to
edit the cells before returning to the original sub and repeating

My code does what 1 asks.


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


wrote in message
ups.com...
Bob

Sorry this appears not to have done the trick.

I'll repeat again. I want to loop through my worksheet until I find a
set of values or constants i.e. populated cells. Once this is done I
want to select the whole region at which I'll activate another sub to
edit the cells before returning to the original sub and repeating

Thanks

Simon
Bob Phillips wrote:
Public Sub ProcessData()
Dim i As Long
Dim iLastRow As Long
Dim iStart As Long
Dim iNextRow As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
iStart = 1
iNextRow = 1
For i = 1 To iLastRow + 1
If .Cells(i, "A").Value = "" Then
.Cells(iStart, "A").Resize(i - iStart).EntireRow.Copy _
Worksheets("Sheet2").Range("A" & iNextRow)
iNextRow = iNextRow + i - iStart
Do While i <= iLastRow And .Cells(i + 1, "A").Value =
""
i = i + 1
Loop
iStart = i + 1
End If
Next i

End With

End Sub

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


wrote in message
ups.com...
I have a sheet with values similar to this:
148 407 118
118 10003 169 364 15
15 222 67
67 296 69
69 298 24
24 291 177


148 407 118
118 10003 169 10004 20 223 13
13 213 121
121 370 124
124 374 145


186 452 12
12 212 50
50 268 47
47 10018 74 10084 58
58 285 141
141 399 143

I wish to select each region or set of values i.e there are three in
the example given above before cuting the values and copying to a new
sheet. I need a macro that can loop through the sheet, find a value
(i.e. anything that isn't blank) within a cell to identify a new
region, select that region, cut to a new sheet, return to the original
sheet before continuing with again.
Thanks

SelectRegion Loop
 

Hi Bob

Many Thanks - yes you are right I seem to have changed specification!

I have got the first sub working now, I would be grateful if you could
assist me with the second version

Version 2

find a
set of values or constants i.e. populated cells. Once this is done I
want to select the whole region at which I'll activate another sub to
edit the cells before returning to the original sub and repeating


Thanks

Simon

Bob Phillips wrote:
You've changed the spec

Version 1.

find a value
(i.e. anything that isn't blank) within a cell to identify a new
region, select that region, cut to a new sheet, return to the original
sheet before continuing with again.

Version 2

find a
set of values or constants i.e. populated cells. Once this is done I
want to select the whole region at which I'll activate another sub to
edit the cells before returning to the original sub and repeating

My code does what 1 asks.


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


wrote in message
ups.com...
Bob

Sorry this appears not to have done the trick.

I'll repeat again. I want to loop through my worksheet until I find a
set of values or constants i.e. populated cells. Once this is done I
want to select the whole region at which I'll activate another sub to
edit the cells before returning to the original sub and repeating

Thanks

Simon
Bob Phillips wrote:
Public Sub ProcessData()
Dim i As Long
Dim iLastRow As Long
Dim iStart As Long
Dim iNextRow As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
iStart = 1
iNextRow = 1
For i = 1 To iLastRow + 1
If .Cells(i, "A").Value = "" Then
.Cells(iStart, "A").Resize(i - iStart).EntireRow.Copy _
Worksheets("Sheet2").Range("A" & iNextRow)
iNextRow = iNextRow + i - iStart
Do While i <= iLastRow And .Cells(i + 1, "A").Value =
""
i = i + 1
Loop
iStart = i + 1
End If
Next i

End With

End Sub

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


wrote in message
ups.com...
I have a sheet with values similar to this:
148 407 118
118 10003 169 364 15
15 222 67
67 296 69
69 298 24
24 291 177


148 407 118
118 10003 169 10004 20 223 13
13 213 121
121 370 124
124 374 145


186 452 12
12 212 50
50 268 47
47 10018 74 10084 58
58 285 141
141 399 143

I wish to select each region or set of values i.e there are three in
the example given above before cuting the values and copying to a new
sheet. I need a macro that can loop through the sheet, find a value
(i.e. anything that isn't blank) within a cell to identify a new
region, select that region, cut to a new sheet, return to the original
sheet before continuing with again.
Thanks