Copy Cell Value into following rows

[ARJUNDSOUZA]

Hi Team,

I am looking for vba code to complete the following task (data sample attached)

Sheet1 - Data File - is my Daily Extract File.

Sheet2 - Task 1:
Column A has values while there are blank rows in between, I want to know a vba code which can copy the values in Col A and paste in the blank cells
Copy A1 value "BEST AVAILABLE" and paste in A2,A3,A4, (these can be 20-30 rows, while sometimes 10 also)
then A5 has "CONSORTIA", copy and paste this to cells A6, A7 (same as above)
then A8 has "CORPORATE MARKETING PROGRAMS" copy as paste this to cells A9:A23 and so on....

Have trimmed my data file as this sheet has average of 700+ rows to be formatted.
Towards the end need the value of Col A to be copied only until there is any value in corresponding Col B

Sheet3 - Task2
Look in Column B - if any row has "Numeric Value" then delete the entire row.

Finally I want the sheet to be completed as shown in Sheet3 "Task2"

ps: All above tasks to be completed in Sheet1 itself, I had created the additional sheets only to explain the whole process.

Thank you in advance for your help.

 

[Marc L]

Hi !​

​

According to your attachment a first beginner starter demonstration to paste to the data worksheet module :​

Sub Demo1()
            Dim Rg As Range, Rp As Range
            Set Rg = Cells(Me.UsedRange.Rows.Count + 1, 1)
    If IsEmpty(Rg(0)) Then
            Application.ScreenUpdating = False
        Do
            Set Rp = Rg(0)
            Set Rg = Rg.End(xlUp)
            Rg.AutoFill Range(Rg, Rp), xlFillCopy
        Loop Until Rg.Row = 1
            Set Rp = Nothing
            Application.ScreenUpdating = True
    End If
            Set Rg = Nothing
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Marc L]

According to your attachment another beginner starter demonstration to paste to the data worksheet module :​

Sub Demo2()
       Dim C&
    With Me.UsedRange.Rows
           C = Application.Count(.Columns(2))
        If C Then
            Application.ScreenUpdating = False
           .Columns(3).Formula = "=ISNUMBER(B1)"
           .Resize(, 3).Sort .Cells(1, 3), xlAscending, Header:=xlNo
           .Item(.Count - C + 1 & ":" & .Count).Clear
           .Columns(3).Clear
            Application.ScreenUpdating = True
        End If
    End With
End Sub

You may Like it !
​

 

[Marc L]

My first demonstration revamped to paste to the data worksheet module :​

Sub Demo1r()
                 Dim Rg As Range
    With Me.UsedRange.Columns(1)
        If Application.CountBlank(.Cells) Then
                Application.ScreenUpdating = False
            For Each Rg In .Cells.SpecialCells(xlCellTypeBlanks).Areas
                     Rg(0).Copy Rg
            Next
                Application.ScreenUpdating = True
        End If
    End With
End Sub

You should Like it !​

​

 

[ARJUNDSOUZA]

Hi Marc,

Thanks once again for your reply, however I am getting an error as follows

Compile error:

Invalid use of Me keyword.

Could you please share how I can resolve this.

Looking forward..

Kind regards
Arjun

 

[Marc L]

Your bad ! Just well read and apply the bold dark red direction before the code, the reason why I wrote it …​

​