Macro to delete rows
 

I have a list of daily data that is one day per row, I need a macro to trim
it back from daily data to weekly data retaining only Friday's data for each
week, so I need to delete 4 rows, skip a row and delete the next 4 rows etc.
The list is currently about 400 rows. (Excel 2003)

Macro to delete rows
 

OssieMac:

It looks like we are on the "same page". We must have posted seconds apart.

--
Rich Locus
Logicwurks, LLC


"Rich Locus" wrote:

Hello:

If this answers your question, please check "Answered" on the site.

Unless you are absolutely sure that you will have exactly 5 days per week,
it could be dangerous just to delete every 5th row.

I would recommend looking at the date, and if it's a Friday, then don't
delete it. This little example should give you a good start. It searches
from row 21 to row 1 looking at dates in column A, and if they are not a
Friday, it deletes the line.

Option Explicit

Public Sub DayOfWeek()
Dim i As Long
Dim DayOfWeek As Integer '6 = Friday

For i = 21 To 1 Step -1
DayOfWeek = WorksheetFunction.Weekday(Cells(i, 1).Value, 1)
If DayOfWeek < 6 Then
Rows(i).Delete
End If
Next i

End Sub

--
Rich Locus
Logicwurks, LLC


"Ken G" wrote:

I have a list of daily data that is one day per row, I need a macro to trim
it back from daily data to weekly data retaining only Friday's data for each
week, so I need to delete 4 rows, skip a row and delete the next 4 rows etc.
The list is currently about 400 rows. (Excel 2003)

Macro to delete rows
 

Ken, refer
http://www.microsoft.com/wn3/locales...eAPostAsAnswer

--
Jacob (MVP - Excel)


"Ken G" wrote:

Thanks OssieMac and Rich Locus. Good point about checking the day rather
than just deleting 4 days and skip one. It worked perfectly. I've clicked the
"Yes" boxes for "Did this answer the question?" but not sure what you meant
by
"... please check "Answered" on the site",

Thanks,
"
"Rich Locus" wrote:

Hello:

If this answers your question, please check "Answered" on the site.

Macro to delete rows
 

Instead of deleting why not just filter on Friday...
However, if you insist, work from the bottom up

Sub deleteallbutfriday()
mc = "a" 'change to suit
For i = Cells(Rows.Count, mc).End(xlUp).Row To 5 Step -5
Rows(i - 4).Resize(4).Delete
Next i
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ken G" wrote in message
...
I have a list of daily data that is one day per row, I need a macro to trim
it back from daily data to weekly data retaining only Friday's data for
each
week, so I need to delete 4 rows, skip a row and delete the next 4 rows
etc.
The list is currently about 400 rows. (Excel 2003)