Macro for copy paste until blank [Solved/Closed]

J - Mar 5, 2010 at 09:42 AM - Latest reply: rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen
- Mar 13, 2010 at 11:20 AM
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
See more 

6 replies

Best answer
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Mar 5, 2010 at 10:32 AM
1
Thank you
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?

Thank you, rizvisa1 1

Something to say? Add comment

CCM has helped 1704 users 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
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen > J - Mar 5, 2010 at 12:06 PM
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
J > rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Mar 5, 2010 at 12:10 PM
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
Anil > rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Mar 13, 2010 at 11:13 AM
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
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen > Anil - Mar 13, 2010 at 11:20 AM
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