Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
For Each ... Next loop - need to reference the loop variable | Excel Programming | |||
Advancing outer Loop Based on criteria of inner loop | Excel Programming | |||
Loop Function unable to loop | Excel Programming | |||
Problem adding charts using Do-Loop Until loop | Excel Programming | |||
HELP!!!! Can't stop a loop (NOT an infinite loop) | Excel Programming |