Autofill unknown amount of cells

Question

I'm trying to write VBA code for the equivalent of click and drag the corner of cell to the end. illustration:

I've tried several different suggestions I've found online but I am always met with errors.

Below is the line I am currently working on. But I am getting Run-time error '1004': Application-defined or object-defined error.

Range("A4").Select
ActiveCell.AutoFill Range(ActiveCell, ActiveCell.Offset(0, 1).End(xlRight).Offset(0, -1))

For more context here is the whole script I am working on: screenshot of whole script:
and here is the original line of script according to my own logic before I started looking for answers online:

Selection.Autofill Destination:=Range("A4":mylastcell_4), Type:=xlFillDefault

mylastcell_4 has been declared at the top of the script (see screenshot of whole script). I've also tried changing to "A4",mylastcell_4 but I still receive the error.

I am extremely new to VBA and have no idea where to go from here.

I've tried

 .Range(.Cells(4, "A"), .Cells(1, .Columns.Count).End(xlToLeft).Offset(3, 0)).Formula = "=concatenate(a2, a1, a3)"

Which is suggested in one of the answers below. However this only gives the result if I have 5 cells in row 1. I require more flexibility as there might be anywhere from 2 values to 500 values to work with. Image of results of above suggested script

I've also found that going along this route seems to destroy my next lot of concatenation that I know worked well separately - hopefully its something simple that needs altering? Script used for next concatenation.

The ultimate goal of this exercise is to get a list of text prepared to go straight into SQL. i.e. a b c d e becomes 'a','b','c','d','e' all in one cell.

Answer 1

The content you want to add is this formula placed in A4.

=concatenate(a2, a1, a3)

Since all three preceding rows are important to the formula, it's inferred that any of the preceding rows could be used for a 'stop' point to the right. I'll use row 1 (3 rows above) and offset down to locate the 'stop' point.

with worksheets("sheet1")
    ...
    .cells(4, "A").formula = "=concatenate(a2, a1, a3)"
    .range(.cells(4, "A"), .cells(1, .columns.count).end(xltoleft).offset(3, 0)).fillright
    ...
end with

I prefer .FillRight for this operation over .AutoFill but you can easily use this method for .AutoFill as well.

Alternately, you could have just written the formula into all four cells at once.

with worksheets("sheet1")
    ...
    .range(.cells(4, "A"), .cells(1, .columns.count).end(xltoleft).offset(3, 0)).formula = "=concatenate(a2, a1, a3)"
    ...
end with

You need to preface each Range and Cells with . like .Range(...) and .Cells(...) in order for your with worksheets("sheet1") to work properly.

In your own version, if mylastcell_4 was set correctly, then this would likely have solved the issue.

.Range("A4").Autofill Destination:=.Range("A4:" & mylastcell_4.address), Type:=xlFillDefault

You set up a With Worksheet ... End With block then never appeared to use it.

Answer 2

The logic of a range in VBA follows this structure:

Range(A1:D1) -> Range(Cells(A1), Cells(D1)) -> 

Range(Cells(row number, column number), Cells(row number, column number)) -> 

Range(Cells(1, 1), Cells(1, 4)) ... And then you can replace 1,1 & 1,4 with variables..

Maybe this works but I think your range might need to be modified/clarified. Is not clear what you are trying to achieve (what do you want the code to do, what is your expected result).., but this could be worth a try:

ActiveCell.AutoFill Destination:=Range(ActiveCell, ActiveCell.Offset(0, 1).End(xlRight).Offset(0, -1))

I want to encourage you, keep on learning VBA :)!!!