Prev: Have VB Code "on" for all workbooks
Next: Odd VLOOKUP Error
From: OssieMac on 27 May 2010 16:06
Hi Ken,

I have assumed that you have a column with dates. If so, then try the
following code. Note the comments where you may need to edit the the Sheet
name and then column identifier for the column with the dates.

If no column with dates then let me know and I will have another look at it.

Sub DeleteExceptFriday()

Dim lastRow As Long
Dim i As Long

'Edit "Sheet1" to your sheet name
With Sheets("Sheet1")
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

'Must work backwards when deleting rows
'in a loop as follows.
'(to 2 assumes column header exists in row 1)
For i = lastRow To 2 Step -1

'Edit "A" to the column with your dates
If Weekday(.Cells(i, "A"), 1) <> 6 Then
.Rows(i).Delete
End If
Next i
End With
End Sub

--
Regards,

OssieMac


"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: Rich Locus on 27 May 2010 16:19
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: Ken G on 27 May 2010 16:22
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: OssieMac on 27 May 2010 17:07
Might need to edit "A" in the following line also to reflect the column with
your dates.

lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

--
Regards,

OssieMac

 | 
Pages: 1
Prev: Have VB Code "on" for all workbooks
Next: Odd VLOOKUP Error