PDA

View Full Version : [SOLVED] dayly data to weekly data



asdzxc
09-01-2013, 05:15 AM
Sub w()
Dim i As Long, rng As Range
Columns(1).Insert
With Range("b2", Range("b" & Rows.Count).End(xlUp)).Offset(, -1)
.Formula = "=if(and(weekday(b2,2)<weekday(b3,2),b3<>""""),1,"""")"
.Value = .Value
On Error Resume Next
.SpecialCells(2, 1).EntireRow.Delete
On Error GoTo 0
End With
Columns(1).Delete
End Sub
When run the above macro on Book1.xlsx, data in col AB and CD both change to weekly one. I want data in col CD intact when
running the macro.

snb
09-01-2013, 08:04 AM
If you use entirerow.delete, it also effects data in column C and D

ashleyuk1984
09-01-2013, 07:01 PM
Instead of entirerow delete, how about this?


Sub w() Dim i As Long, rng As Range
Columns(1).Insert
With Range("b2", Range("b" & Rows.Count).End(xlUp)).Offset(, -1)
.Formula = "=if(and(weekday(b2,2)<weekday(b3,2),b3<>""""),1,"""")"
.Value = .Value
On Error Resume Next
.SpecialCells(2, 1).Offset(0, 1).Delete Shift:=xlUp
.SpecialCells(2, 1).Offset(0, 2).Delete Shift:=xlUp
On Error GoTo 0
End With
Columns(1).Delete
End Sub

asdzxc
09-01-2013, 09:30 PM
Sloved. Thank you

snb
09-02-2013, 02:05 AM
I assume you mean solved ?