Auto fill down a row without copy and pasting

[steve400243]

Hello, Wondering about the best way to fill down a column without copy and pasting. I have multiple entries in a column as shown in the attachment. Would like a way to copy down the rows to the next entry then stop and copy down that entry, etc. Hope that makes sense. Thank you!

RFCU4032547
SMCU1221331
BMOU5992301
YMLU9539086

 

[Gokhan Aycan]

Once you get the ranges sorted...

Dim rngAutoSource as Range
Dim rngAutoDest as Range

Set rngAutoSource = Range(...)
Set rngAutoDest = Range(...)

rngAutoSource.AutoFill Destination:=rngAutoDest

Set rngAutoSource = Nothing
Set rngAutoDest = Nothing

 

[steve400243]

Thank Gokhan, Cell J2 would be my first entry on the example for RFCU4032547. Sorry but I don't see how to add the ranges in to your example.

 

[Gokhan Aycan]

Before going on, since you want to copy/paste, autofill may increment the numbers at the end. Is that what you want?

Another question, about the last entry, how many copies will be made? Others are easy to find out, but the last one?

 

[steve400243]

Before going on, since you want to copy/paste, autofill may increment the numbers at the end. Is that what you want?

Another question, about the last entry, how many copies will be made? Others are easy to find out, but the last one?

Here would be my desired results(In Red), and this could go down Column J for hundreds of rows.

OOLU7492390
OOLU7492390
OOLU7492390
OOLU7492390
CNSU8047534
CNSU8047534
CNSU8047534
CNSU8047534
CNSU8047534
CNSU8047534
CNSU8047534
CNSU8047534
CNSU8047534
CNSU8047534
HDMU553339
HDMU553339
HDMU553339
HDMU553339
HDMU553339
HDMU553339
HDMU553339
HDMU553339
HDMU553339
HDMU553339
HDMU553339
HDMU553339
HDMU553339
HDMU553339
HDMU553339
HDMU553339
HDMU553339
HDMU553339
HDMU5559647

 

[Gokhan Aycan]

Is there some other column with data that we can get the last row from? As is it is unknown...

 

[steve400243]

Is there some other column with data that we can get the last row from? As is it is unknown...

The Last row would be an entry similar to the others listed, but It would change weekly as to how many rows would be used. It would always start in J2.

 

[Gokhan Aycan]

I assumed last row would be 50, but you need to know what the last row would be. Are there no other columns with data in it?

Public Sub test()
  
    Dim i As Long
    Dim StartRow As Long, EndRow As Long
    Dim CheckRow As Long
    Dim LastRow As Long
  
    LastRow = 50 ' You have to find out this somehow, so instead of a hardcoded 50, this number
                           ' should be calculated.
  
    StartRow = 2
    CheckRow = 2
  
    Do While CheckRow < LastRow
        CheckRow = Cells(StartRow, "J").End(xlDown).Row
        If Cells(CheckRow, "J").Value <> "" Then
            ' Range end (next element) found
            EndRow = CheckRow - 1
            Range("J" & StartRow + 1 & ":J" & EndRow).Value = Range("J" & StartRow).Value
            StartRow = CheckRow
        Else
            Range("J" & StartRow + 1 & ":J" & LastRow).Value = Range("J" & StartRow)
            Exit Do
        End If
    Loop
  
End Sub

 

[steve400243]

I assumed last row would be 50, but you need to know what the last row would be. Are there no other columns with data in it?

Public Sub test()
 
    Dim i As Long
    Dim StartRow As Long, EndRow As Long
    Dim CheckRow As Long
    Dim LastRow As Long
 
    LastRow = 50 ' You have to find out this somehow, so instead of a hardcoded 50, this number
                           ' should be calculated.
 
    StartRow = 2
    CheckRow = 2
 
    Do While CheckRow < LastRow
        CheckRow = Cells(StartRow, "J").End(xlDown).Row
        If Cells(CheckRow, "J").Value <> "" Then
            ' Range end (next element) found
            EndRow = CheckRow - 1
            Range("J" & StartRow + 1 & ":J" & EndRow).Value = Range("J" & StartRow).Value
            StartRow = CheckRow
        Else
            Range("J" & StartRow + 1 & ":J" & LastRow).Value = Range("J" & StartRow)
            Exit Do
        End If
    Loop
 
End Sub

View attachment 45328

I think I see what you mean, I would need to change the last row number each time as I would never know until the Data is sent to me. I just tested with that and it seems to work as needed. Thank you for your Code! Much Appreciated.

 

[Gokhan Aycan]

Surely there must be other columns with data. If you can provide the column letter for that we can find it programmatically.
Example: