Macro for copy paste until blank [Solved/Closed]

Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello, I have a weekly data dump in a text file and have to clean it up. It gives me all of the important customer names (CAN100) in this case and then has a blank or other junk, then goes to another customer name. Is there a macro to copy the first cell then copy till a blank, and then repeat?

CAN100
R

CAP150
R
R
R

CAR275
*Op R
*Op R
*Op R
*Op R
*Op R
R

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
Based on your sample data, if the macro runs, how would it look like. Also how one can say this is a customer name and this is junk?
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2804 users have said thank you to us this month

I was wondering if there was a way to take the first cell after a blank and copy and paste that till blank, then repeat. I have a formula that I've used before that mimic this, but I wanted to have it in a macro.

If I create a new column next to the data for my formula
=if(the cell above customer name =0,customer name, cell above this one)

And the result is this


SUN150 SUN150
R SUN150
R SUN150
R SUN150
R SUN150
R SUN150
R SUN150
R SUN150
R SUN150
R SUN150
SUN150
SUN400 SUN400
R SUN400
R SUN400
R SUN400
R SUN400
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760 > J
But the sample data and expected result have no co-relation

Also you have not mentioned, how can I say that "R" is not a customer name ?

Any how, you may have to modify this a bit based on answers

Assumption is data is starting from Row 1
Sub fillUp()

Dim lLastRow As Long
Dim lCounter As Long
Dim sThisCust As String

    lLastRow = Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
            
    sThisCust = Cells(1, "A")
    Cells(1, "B") = sThisCust
    
    For lCounter = 2 To lLastRow
        
        Cells(lCounter, "B") = sThisCust
        
        If Trim((Cells(lCounter, "A")) = "") Then
            sThisCust = Cells(lCounter + 1, "A")
        End If
    Next
    
End Sub
>
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020

The R is file status, which I don't need on this line as I copy that column. I'll try this macro you suggested, thanks. I just wanted it to take the first value and copy paste till blank. It doesn't need to know what it means I guess
>
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020

Hi,

Please suggest me a macro to use based on your above quey.

Example
Anil
blank
blank
blank
rajesh
blank
blank
blank
blank
blank
blank
Amit
Blank

i want the first row like to fillup the nest three blank and than take up the rajesh and fillup the next 6 blank and continue
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760 > Anil
Sub fillUp()

Dim lLastRow As Long
Dim lCounter As Long
Dim sThisCust As String

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

sThisCust = Cells(1, "A")

For lCounter = 2 To lLastRow

If Trim((Cells(lCounter, "A")) = "") Then

Cells(lCounter, "A") = sThisCust
else
sThisCust = Cells(lCounter, "A")
End If
Next

End Sub