Copy and Paste VBA

[Learning Excel]

Hi,
I have a datasheet with 5000 rows.
There is week number in column E, starting from 1 to 52 in different rows
What I want is to populate the week in this column to Column A and populate till the next week number is found.
This will allow me to do sorting etc.
Appreciate some guidance on this.

Before ( Date in Column B through D are not the same, for illustration only)

A B C D E

Blank X Y Z 1
Blank X Y Z
Blank X Y Z
Blank X Y Z 2
Blank X Y Z
Blank X Y Z 3
Blank X Y Z

What I want

A B C D E

1 X Y Z 1
1 X Y Z
1 X Y Z
2 X Y Z 2
2 X Y Z
3 X Y Z 3
3 X Y Z

Attempt to write a macro, but is not what I want.
Sub AddWeek()


FinalRow = Cells(5000, 5).End(xlUp).Row
For i = FinalRow To 7 Step -1
If Cells(i, 5).Value >= "1" Then
Cells(i, 5).Copy Destination:=Cells(i, 1)
End If
Next i

End Sub

Thank
Learning Excel

 

[Andrew Poulsom]

Try this:

Sub Test()
    Dim Rng As Range
    Set Rng = Range("A2:A" & Range("B65536").End(xlUp).Row)
    With Rng
        .FormulaR1C1 = "=MAX(R2C[4]:RC[4])"
        .Value = .Value
    End With
End Sub

The code assumes your data starts in row 2 and that you want to fill to the last used row in column B. Amend if necessary.

 

[KenWright]

No need for a macro.

Copy Col E and paste into Col A. Select all Col A and do edit / Go To / Special / Blanks, then type = and hit the Up Arrow once and use CTRL+ENTER to enter the data.

If you want to get rid of the formulas just copy Col A and paste special as values.


Addendum

Your data looks ripe for a Pivot table, and Pivot Table functionality will take you only a few minutes to set up. Depending on what analysis you need to do it could be the best few minutes you ever spent (Fantastic thing to learn no matter what though, and yet so simple).

If you were interested then check out the following links that will give you a step by step guied on how to do this as well as examples of what you can get out:-

http://peltiertech.com/Excel/Pivots/pivotstart.htm

 

[Learning Excel]

Hi,
Thank you for looking into my problem.
I believe the sample that I have provided is distorting, it should be :-

A B C D E

Blank X Y Z 1
Blank X Y Z Apple
Blank X Y Z Orange
Blank X Y Z 2
Blank X Y Z Grape
Blank X Y Z 3
Blank X Y Z Lemon

What I want

A B C D E

1 X Y Z 1
1 X Y Z
1 X Y Z
2 X Y Z 2
2 X Y Z
3 X Y Z 3
3 X Y Z

There are data in between the week(Column E) that I do not want to remove.
There are actually more Columns which is up to Column Z instead of E as an example.

Your proposal have given me an idea. That is Column E to Column A
Remove anything that is not number than do a F5 fill blank cell.
This may not be effective but should do the work.


Learning Excel

 

[Andrew Poulsom]

The code I posted should do what you want (it did when I tested it).