Macro for copy paste until blank

Solved/Closed

J - Mar 5, 2010 at 09:42 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - 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

Macro for copy paste until blank
Spell number in excel without macro - Guide
Macro excel download - Download - Spreadsheets
How to paste photo in resume - Guide
Automatically run a macro when opening a worksheet - Guide
Excel macro to create new sheet based on value in cells - Guide

1 response

Answer 1 / 1

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Mar 5, 2010 at 10:32 AM

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?

J
Mar 5, 2010 at 10:50 AM

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 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766 > 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 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022
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 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022
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 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766 > 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

Similar discussions

Can no longer paste text into insta

Deleted Message icon

Formula to Find&replace blank cells in excel

Comparing Two Excel Sheets and copying like data to third sheet

My Facebook Page is Loading Blank

Similar discussions

Subscribe To Our Newsletter!