Previous Topic Next Topic

Automate The Process

Closed
Avy - 17 Sep 2010 à 02:52
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 - 17 Oct 2010 à 13:03
Hello,

I have this problem on how to make this in to just one procedure.

Range is C:C

now as you can see it selects a range and copy its data and paste it to the next empty cells
Is it possible to do this is with one procedure that will copy and paste? It will stop at the last empty cell or C1000 to be exact, just to control the paste thing.

Range("C2").Select ----DATA TO BE COPIED
Selection.Copy
Range("C3").Select
Range(Selection, Selection.End(xlDown)).Select
Range("C3:C11").Select ------NEXT EMPTY CELL BELOW
ActiveSheet.Paste
Application.CutCopyMode = False

Selection.End(xlDown).Select ---- ANOTHER DATA TO BE COPIED AND SO ON....
Selection.Copy
Range("C15").Select
Range(Selection, Selection.End(xlDown)).Select
Range("C15:C17").Select ------NEXT EMPTY CELL BELOW
ActiveSheet.Paste
Application.CutCopyMode = False


Selection.End(xlDown).Select
Selection.Copy
Range("C19").Select
Range(Selection, Selection.End(xlDown)).Select
Range("C19:C22").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Selection.End(xlDown).Select
Selection.Copy
Range("C24").Select
Range(Selection, Selection.End(xlDown)).Select
Range("C24:C29").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Done until the last data to be copied.
Any help is good.
Please advise me what to do with this.
Thanks
Related:

5 responses

Réponse 1 / 5
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
19 Sep 2010 à 07:56
Could you please upload a sample EXCEL file WITH sample data, macro, formula , conditional formatting etc on some shared site like https://authentification.site , http://docs.google.com, http://wikisend.com/ , http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too
Réponse 2 / 5
avy
19 Sep 2010 à 21:13
Hi,

What Im trying to do is like this:


data1
blank cell
blank cell
data2
blank cell
blank cell
blank cell
data3
blank cell

output should be
data1
data1
data1
data2
data2
data2
data2
data3
data3

it will just copy the data on the next blank cell(downward). after it paste the data1 it will proceed to data2 and so on.

This is what Im trying to do. I have no idea how to put this on loop.
Thanks
Réponse 3 / 5
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
20 Sep 2010 à 08:47
if all you want to do is fill in the missing value with the last value, then you can try this 

    Dim lRow As Long
    Dim lLastRow As Long
    Dim lLastRow As Long
    
    lLastRow = Cells(Rows.Count, "C").End(xlUp).Row
    If (Cells(Rows.Count, "C") <> vbNullString) Then lLastRow = Rows.Count
    lLastRow = 1
    For lRow = 1 To lLastRow
        If Cells(lRow, "C") <> Cells(lLastRow, "C") _
        Then
            lLastRow = lRow
        ElseIf (Cells(lRow, "C") = vbNullString) _
        Then
            Cells(lRow, "C") = Cells(lLastRow, "C")
        End If
    Next lRow
Réponse 4 / 5
Avy
20 Sep 2010 à 21:52
Having errors in putting the copy and paste command. Where should I put it in the Loop?

I put the copy command in the first if loop. the trouble is in the paste thing.

Thanks


/////if all you want to do is fill in the missing value with the last value, then you can try this

Dim lRow As Long
Dim lLastRow As Long
Dim lLastRow As Long

lLastRow = Cells(Rows.Count, "C").End(xlUp).Row
If (Cells(Rows.Count, "C") <> vbNullString) Then lLastRow = Rows.Count
lLastRow = 1
For lRow = 1 To lLastRow
If Cells(lRow, "C") <> Cells(lLastRow, "C") _
Then
lLastRow = lRow
ElseIf (Cells(lRow, "C") = vbNullString) _
Then
Cells(lRow, "C") = Cells(lLastRow, "C")
End If
Next lRow
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
21 Sep 2010 à 01:54
what error?

here is the full routine 
Sub ReplicateCell()  
    Dim lRow As Long  
    Dim lLastRow As Long  
    Dim lMaxRow As Long  
    Dim Cell As Range  
      
    Set Cell = Cells.Find("*", Cells(1, 1), , , xlByRows, xlPrevious)  
    If Cell Is Nothing Then Exit Sub  
    lMaxRow = Cell.Row  
    Set Cell = Nothing  
    lLastRow = 1  
    For lRow = 1 To lMaxRow  
        If (Cells(lRow, "C") = vbNullString) _  
        Then  
           Cells(lRow, "C") = Cells(lLastRow, "C")  
        ElseIf Cells(lRow, "C") <> Cells(lLastRow, "C") _  
        Then  
            lLastRow = lRow  
        End If  
    Next lRow  
End Sub
Réponse 5 / 5
Avy
29 Sep 2010 à 21:21
sorry for late reply.

Im talking about the copy and paste routine, Where will I insert it?

Thanks
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
17 Oct 2010 à 13:03
that was supposed to replace your existing code. if my memory serves me for a change, you had a routine by that name

Similar discussions

Sound picker cached background process
Badchad -
Ambucias -

3 responses
an error occured, please try again later
adrian -
Farman -

35 responses