Selecting consecutive rows in VBA

[Victor H]

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.

 

[Jim Rech]

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
|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.

 

[Guest]

'>>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

 

[Victor H]

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 !!!

 

[Jim Rech]

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
| 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
| >| >|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.
| >
| >
| >.
| >

 

[Victor H]

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)

 

[Victor H]

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
| 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
| >| >|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.
| >
| >
| >.
| >


.