I need to copy the data from the top row of a worksheet to all those empty rows underneath it UNTILL you get to a row that has data…then start over and copy that new data into the empty rows….and so on…

I have the code that we've written so far attached and below is an example of the data (a row with info...then blank rows that need to be filled with the above info...)

I'm not so good at this, but if anyone has any suggestions, please let me know
THanks
Jen

16296 INV BALLESTER CONSULTI 4541 29-Jun-07


13518 INV BEST RESEARCH 4271 23-Apr-07


13963 INV BLAUW RESEARCH 4298 30-Apr-07


14250 INV BLAUW RESEARCH 4298 15-May-07

10889 INV BRAINJUICER BV 2270 25-Jan-07

Attachments
Sub fill2()
'
' fill2 Macro
'
' Keyboard Shortcut: Ctrl+Shift+B
'
    Range("B1:F1").Select
    Selection.Copy
    Range(Selection, Selection.End(xlDown)).Select
    Range("B1:F8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.End(xlDown).Select
    
    Range("B9:F9").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Range(Selection, Selection.End(xlDown)).Select
    Range("B9:F18").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    
    For x = 1 To 100
    Selection.End(xlDown).Select
    Range("B19:F19").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range(Selection, Selection.End(xlDown)).Select
    Range("B19:F25").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.End(xlDown).Select
    
    
    Next x
End Sub

hi,

Try below coding:

Sub fill2()
'
' fill2 Macro
'

'

   Declare the Variables
    Dim intRng As Integer 'Get the Range of rows from the user
    Dim strStart As String  'Assign the Starting Range
    Dim strEnd As String 'Assign the Ending Range
    Dim intI As Integer 'For Loop
    Dim strRange As String 'Concatenate the strStart and strEnd 
    Dim strVal As String 'Get the Value of the cell

    'Get the input from the use for no. of rows
    intRng = InputBox("Enter the No. of Rows?", "Fill the Blank")
    
     For Loop
    For i = 1 To intRng
        strStart = "A" & i Starting Range
        strEnd = "F" & i Ending Range
        strRange = strStart & ":" & strEnd Concatenation of strStart and strEnd
        strVal = Cells(i, 1)      'Value of the Cell
        If strVal <> "" Then      ' If the cell contain the values
            Range(strRange).Select     'Select the Row
            Selection.Copy      'Copy the row
        Else
           'The below process will be done until the cell is blank
            Range(strRange).Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
        End If
    Next
End Sub

Have a nice Day

:) Shailaja:)


I need to copy the data from the top row of a worksheet to all those empty rows underneath it UNTILL you get to a row that has data…then start over and copy that new data into the empty rows….and so on…

I have the code that we've written so far attached and below is an example of the data (a row with info...then blank rows that need to be filled with the above info...)

I'm not so good at this, but if anyone has any suggestions, please let me know
THanks
Jen

16296 INV BALLESTER CONSULTI 4541 29-Jun-07


13518 INV BEST RESEARCH 4271 23-Apr-07


13963 INV BLAUW RESEARCH 4298 30-Apr-07


14250 INV BLAUW RESEARCH 4298 15-May-07

10889 INV BRAINJUICER BV 2270 25-Jan-07

hey i could help you but i cant understand what u want.. post here a sample data and your expected data after running the macro.. then put even alittle explaination.. then ill send you your code.. tnx

This article has been dead for over six months. Start a new discussion instead.