Unique cell value should gets copy to below cell

[harinsh]

Hello, I am looking for macro to copy and paste the unique values in one cell to below cells till next new value found. Plz help on this Thank you

Here is the example:

Column E
ST00001

BTA1
ST00002

ST00003
BTA2

ST00004

End......

Output Should be.
ST00001
ST00001
BTA1
ST00002
ST00002
ST00003
BTA2
BTA2
ST00004
ST00004
End......

 

[btomjack]

Does it have to be a macro? You can get a similar result by doing this:
1. Select the entire column
2. Press F5
3. The "Go To" dialog box will appear. Click the "Special..." button in the lower left corner
4. Select the radio button for "Blanks" and click "OK"
5. In the first cell, type the formula to make that cell equal to the cell above it
6. Press the keys CTRL and ENTER at the same time

If the cells aren't blank, you might run into some problems, but this will save you from trying to write a macro for it.

 

[harinsh]

Thanks for your reply....I have tried the way you explained in the below...however I did not get the results...could you please suggest any alternative way....Thanks again

 

[btomjack]

What was the problem you encountered?

Sometimes, when I use this technique, the cells are not truly blank. The result is that none of the cells are selected because Excel thinks there is something in them. To remedy this problem, I select the data I want and run this code:

Sub RemoveDuffs()
    Dim LastRow As Long, LastCol As Integer, cell As Range
     
    LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
    LastCol = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column
     
    For Each cell In Range(Cells(1, 1), Cells(LastRow, LastCol))
        If IsEmpty(cell) = False And Len(cell) = 0 Then cell.Clear
    Next cell
     
End Sub

Then I am able to re-try the previously mentioned technique, and it works just fine.