Prev: MODI printing and combining files
Next: "Do you want to update" msg
From: Ken G on 27 May 2010 17:20
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)
From: Rich Locus on 27 May 2010 17:59
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)
From: Jacob Skaria on 27 May 2010 18:01
Ken, refer
http://www.microsoft.com/wn3/locales/help/help_en-us.htm#RateAPostAsAnswer

--
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.
> > >
>
From: Don Guillett on 27 May 2010 18:20
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
dguillett(a)gmail.com
"Ken G" <KenG(a)discussions.microsoft.com> wrote in message
news:AFFC2143-AE89-432A-A7A5-5CFBE222B286(a)microsoft.com...
>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)

 | 
Pages: 1
Prev: MODI printing and combining files
Next: "Do you want to update" msg