Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting consecutive rows in VBA
I have a worksheet with rows covering 14 columns from (Axx
to Nxx). The first row contains a date. The other rows contain miscellaneous other data. Out of all the rows already in the worksheet, I need to select those rows (they are consecutive) that satisfy the following criteria: Select row if Axx = Today()-1 (Axx is already formatted as a Date) In other words, I'd like to select all rows with yesterday's date and paste them to another worksheet. I've already written a macro that does that, but I have to select the rows myself before the macro copies and pastes. That macro would look a lot better if it could work unattended. Thanks in advance for any help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting consecutive rows in VBA
This should do the copy. The matching rows do not have to be consecutive.
Sub a() Dim Cell As Range Dim Rg As Range For Each Cell In Range(Range("a2"), Range("A65536").End(xlUp)) If Cell.Value2 = CLng(Date) - 1 Then If Rg Is Nothing Then Set Rg = Cell.Resize(1, 14) Else Set Rg = Union(Rg, Cell.Resize(1, 14)) End If End If Next If Not Rg Is Nothing Then Rg.Copy Worksheets.Add ActiveSheet.Paste Application.CutCopyMode = False End If End Sub -- Jim Rech Excel MVP "Victor H" wrote in message ... |I have a worksheet with rows covering 14 columns from (Axx | to Nxx). | The first row contains a date. The other rows contain | miscellaneous other data. | | Out of all the rows already in the worksheet, I need to | select those rows (they are consecutive) that satisfy the | following criteria: | | Select row if Axx = Today()-1 (Axx is already formatted as | a Date) | | In other words, I'd like to select all rows with | yesterday's date and paste them to another worksheet. | | I've already written a macro that does that, but I have to | select the rows myself before the macro copies and pastes. | That macro would look a lot better if it could work | unattended. | | Thanks in advance for any help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting consecutive rows in VBA
'This codes assumes your dates are sorted.
'When it finds The first matching date it will do a copy for each subsequent row Until It does not find a matching date For Each c In ActiveSheet.UsedRange.Range("A:A") '<< The Column with the Dat If FoundDate = True And C<Today()-1 Then Exit Sub If IsDate(C) And C = Today()-1 Then FoundDate = Tru C.EntireRow.Copy Destination:= Sheets("CopySheet").Rows( Sheets("CopySheet").UsedRange.Rows.Count +1 End I Next C ----- Victor H wrote: ---- I have a worksheet with rows covering 14 columns from (Axx to Nxx) The first row contains a date. The other rows contain miscellaneous other data Out of all the rows already in the worksheet, I need to select those rows (they are consecutive) that satisfy the following criteria Select row if Axx = Today()-1 (Axx is already formatted as a Date In other words, I'd like to select all rows with yesterday's date and paste them to another worksheet I've already written a macro that does that, but I have to select the rows myself before the macro copies and pastes. That macro would look a lot better if it could work unattended Thanks in advance for any help |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting consecutive rows in VBA
Looks like it's working except that I had to change ("A2")
for ("A7"). Also, the macro pastes the selected data onto a blank worksheet that it creates in the existing workbook. I need the data to be appended at the first blank cell available in a separate worksheet named "Summary" I know how to find the first blank cell. Should I replace "Worksheets.Add" by "Window. ("Summary").Activate? The worksheet "Summary" is in a workbook called "Year Summary". The worksheet is open at the time the macro is running. Thanks anyway for your valuable help, you saved me a lot of time !!! -----Original Message----- This should do the copy. The matching rows do not have to be consecutive. Sub a() Dim Cell As Range Dim Rg As Range For Each Cell In Range(Range("a2"), Range ("A65536").End(xlUp)) If Cell.Value2 = CLng(Date) - 1 Then If Rg Is Nothing Then Set Rg = Cell.Resize(1, 14) Else Set Rg = Union(Rg, Cell.Resize(1, 14)) End If End If Next If Not Rg Is Nothing Then Rg.Copy Worksheets.Add ActiveSheet.Paste Application.CutCopyMode = False End If End Sub -- Jim Rech Excel MVP "Victor H" wrote in message ... |I have a worksheet with rows covering 14 columns from (Axx | to Nxx). | The first row contains a date. The other rows contain | miscellaneous other data. | | Out of all the rows already in the worksheet, I need to | select those rows (they are consecutive) that satisfy the | following criteria: | | Select row if Axx = Today()-1 (Axx is already formatted as | a Date) | | In other words, I'd like to select all rows with | yesterday's date and paste them to another worksheet. | | I've already written a macro that does that, but I have to | select the rows myself before the macro copies and pastes. | That macro would look a lot better if it could work | unattended. | | Thanks in advance for any help. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting consecutive rows in VBA
You do not have to actually select either the target workbook, sheet or
cell, but it might be easier to find the right cell if you do, so... If Not Rg Is Nothing Then Rg.Copy Workbooks("Year Summary").Activate Worksheets("Summary").Activate 'Select right cell ActiveSheet.Paste Application.CutCopyMode = False End If -- Jim Rech Excel MVP "Victor H" wrote in message ... | Looks like it's working except that I had to change ("A2") | for ("A7"). | | Also, the macro pastes the selected data onto a blank | worksheet that it creates in the existing workbook. | I need the data to be appended at the first blank cell | available in a separate worksheet named "Summary" | I know how to find the first blank cell. | | Should I replace "Worksheets.Add" by "Window. | ("Summary").Activate? | The worksheet "Summary" is in a workbook called "Year | Summary". The worksheet is open at the time the macro is | running. | | Thanks anyway for your valuable help, you saved me a lot | of time !!! | | | -----Original Message----- | This should do the copy. The matching rows do not have | to be consecutive. | | Sub a() | Dim Cell As Range | Dim Rg As Range | For Each Cell In Range(Range("a2"), Range | ("A65536").End(xlUp)) | If Cell.Value2 = CLng(Date) - 1 Then | If Rg Is Nothing Then | Set Rg = Cell.Resize(1, 14) | Else | Set Rg = Union(Rg, Cell.Resize(1, 14)) | End If | End If | Next | If Not Rg Is Nothing Then | Rg.Copy | Worksheets.Add | ActiveSheet.Paste | Application.CutCopyMode = False | End If | End Sub | | | -- | Jim Rech | Excel MVP | "Victor H" wrote in message | ... | |I have a worksheet with rows covering 14 columns from | (Axx | | to Nxx). | | The first row contains a date. The other rows contain | | miscellaneous other data. | | | | Out of all the rows already in the worksheet, I need to | | select those rows (they are consecutive) that satisfy | the | | following criteria: | | | | Select row if Axx = Today()-1 (Axx is already formatted | as | | a Date) | | | | In other words, I'd like to select all rows with | | yesterday's date and paste them to another worksheet. | | | | I've already written a macro that does that, but I have | to | | select the rows myself before the macro copies and | pastes. | | That macro would look a lot better if it could work | | unattended. | | | | Thanks in advance for any help. | | | . | |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting consecutive rows in VBA
Thanks Chris.
-----Original Message----- 'This codes assumes your dates are sorted. 'When it finds The first matching date it will do a copy for each subsequent row Until It does not find a matching date. For Each c In ActiveSheet.UsedRange.Range("A:A") '<< The Column with the Date If FoundDate = True And C<Today()-1 Then Exit Sub If IsDate(C) And C = Today()-1 Then FoundDate = True C.EntireRow.Copy Destination:= Sheets ("CopySheet").Rows( Sheets ("CopySheet").UsedRange.Rows.Count +1) End If Next C ----- Victor H wrote: ----- I have a worksheet with rows covering 14 columns from (Axx to Nxx). The first row contains a date. The other rows contain miscellaneous other data. Out of all the rows already in the worksheet, I need to select those rows (they are consecutive) that satisfy the following criteria: Select row if Axx = Today()-1 (Axx is already formatted as a Date) In other words, I'd like to select all rows with yesterday's date and paste them to another worksheet. I've already written a macro that does that, but I have to select the rows myself before the macro copies and pastes. That macro would look a lot better if it could work unattended. Thanks in advance for any help. . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting consecutive rows in VBA
Looks good....
Thanks again.. -----Original Message----- You do not have to actually select either the target workbook, sheet or cell, but it might be easier to find the right cell if you do, so... If Not Rg Is Nothing Then Rg.Copy Workbooks("Year Summary").Activate Worksheets("Summary").Activate 'Select right cell ActiveSheet.Paste Application.CutCopyMode = False End If -- Jim Rech Excel MVP "Victor H" wrote in message ... | Looks like it's working except that I had to change ("A2") | for ("A7"). | | Also, the macro pastes the selected data onto a blank | worksheet that it creates in the existing workbook. | I need the data to be appended at the first blank cell | available in a separate worksheet named "Summary" | I know how to find the first blank cell. | | Should I replace "Worksheets.Add" by "Window. | ("Summary").Activate? | The worksheet "Summary" is in a workbook called "Year | Summary". The worksheet is open at the time the macro is | running. | | Thanks anyway for your valuable help, you saved me a lot | of time !!! | | | -----Original Message----- | This should do the copy. The matching rows do not have | to be consecutive. | | Sub a() | Dim Cell As Range | Dim Rg As Range | For Each Cell In Range(Range("a2"), Range | ("A65536").End(xlUp)) | If Cell.Value2 = CLng(Date) - 1 Then | If Rg Is Nothing Then | Set Rg = Cell.Resize(1, 14) | Else | Set Rg = Union(Rg, Cell.Resize(1, 14)) | End If | End If | Next | If Not Rg Is Nothing Then | Rg.Copy | Worksheets.Add | ActiveSheet.Paste | Application.CutCopyMode = False | End If | End Sub | | | -- | Jim Rech | Excel MVP | "Victor H" wrote in message | ... | |I have a worksheet with rows covering 14 columns from | (Axx | | to Nxx). | | The first row contains a date. The other rows contain | | miscellaneous other data. | | | | Out of all the rows already in the worksheet, I need to | | select those rows (they are consecutive) that satisfy | the | | following criteria: | | | | Select row if Axx = Today()-1 (Axx is already formatted | as | | a Date) | | | | In other words, I'd like to select all rows with | | yesterday's date and paste them to another worksheet. | | | | I've already written a macro that does that, but I have | to | | select the rows myself before the macro copies and | pastes. | | That macro would look a lot better if it could work | | unattended. | | | | Thanks in advance for any help. | | | . | . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula from consecutive rows to alternate rows? | Excel Discussion (Misc queries) | |||
Transposing data from consecutive rows into non-consecutive rows | Excel Discussion (Misc queries) | |||
copy non-consecutive rows | Excel Discussion (Misc queries) | |||
PULLING OUT CONSECUTIVE ROWS | Excel Discussion (Misc queries) | |||
Selecting Non-Consecutive Variables | Excel Discussion (Misc queries) |