Use VBA to Autofill a Row until the end of the number of data in another row

[Kai El Harrar]

Hello,

I need some help with the following problem:

The Macro should select the first cell with the vlookup (AY2) and autofill the complete range in the column AY until the last row that contain data in the cell next to it (Column E). Column E is the cell that the vlookup refers to.

The situation looks like this:

 The code that I have so far looks like this:

    Sheets(3).Select
    Range("AY2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-41],DennisAR!C[-50],1,0)"
    Selection.AutoFill Destination:=Range("AY2:AY1662")
    Range("AY2:AY1662").Select

The problem with this is, that the number of rows with data always change every week. So I cannot use a static row number of 1662.

I am looking for a way to make Destination:=Range("AY:AY1662) dynamic. In fact it has to refer to the number of rows with data in column E.

Thank you very much in advance,

Kai
   

[#2]

Replace these lines of code:

Selection.AutoFill Destination:=Range("AY2:AY1662")
Range("AY2:AY1662").Select

With this:

Selection.AutoFill Destination:=Range("AY2:AY" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select

Give it a try!

And provide me with any feedback!

[#3]

@Haytham Amairah 

Hi Haytham Amairah,

I was facing similar issues and chanced upon this thread. If i have two columns (O and P) that i wish to autofill via VBA, do I amend the code from: 

Selection.AutoFill Destination:=Range("O2:P313")
Range("O2:P313").Select

to:

Selection.AutoFill Destination:=Range("O2:O" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select

Selection.AutoFill Destination:=Range("P2:" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select

[#4]

Thanks Haytham,
This worked like a magic for me in no time.
Appreciate your knowledge and interest to share it.

[#5]

Thank you Haytham @Haytham Amairah 

[#6]

@Haytham Amairah Dear Haytham, would you be able to amend my code so that it works the same way as OP's please?

I have to amend the ActiveCell.End(xlDown) part so that the formula (in column N) stops at the last row that contains data in column M. Thank you in advance.

ActiveCell.FormulaR1C1 = "=IF(RIGHT(RC[-8],1)=""A"",""f"",""m"")"
ActiveCell.Select
Selection.AutoFill Destination:=Range(ActiveCell, ActiveCell.End(xlDown))
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.Copy
Range(ActiveCell).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Regards,

Mike

[#7]

Hi Mike,

Please try this one:

Sub test()

 ActiveCell.FormulaR1C1 = "=IF(RIGHT(RC[-8],1)=""A"",""f"",""m"")"
 Selection.AutoFill Destination:=Range("N1:N" & Range("M" & Rows.Count).End(xlUp).Row)
 Range(ActiveCell, ActiveCell.End(xlDown)).Select
 Selection.Copy
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
 :=False, Transpose:=False

 Application.CutCopyMode = False

End Sub

Hope that helps

@W4rcloud 

[#8]

Hi

I am trying to build a macro to unprotect sheet, autofill columns from above and protect sheet again. I am using below formula however it is giving error. Can anyone help please?

Sub sbUnProtectSheet()

ActiveSheet.Unprotect "9999"

Set SourceRange = ActiveSheet.Range("B10:H10")
Set fillRange = ActiveSheet.Range("B11:H39")
SourceRange.AutoFill Destination:=fillRange
ActiveSheet.Protect "9999", True, True

End Sub

Thanks

Anil

[#9]

@AnilChhabra

Hi,

It's better to post your question as a new conversation in the community.

If you can, provide us with a sample of the worksheet you're working on!

Regards

[#10]

@Haytham Amairah 

Hey Haytham,

I'm really hoping you can point me in the right direction as well. 

I'm trying to do the same thing as OP, however I have blank rows above and to the left of my data and not sure if that is throwing it off. Below is a screenshot (had to switch all values to xxxx for privacy purposes), I want my Macro to create a new Column to the left of Column J in the screenshot, where a new Column J will be created that will house my formula beginning in the new Cell J6. I then want the Macro to extend this formula down the last row in any of the columns to the left of Column J, as they will all always be populated, however I have been tying everything to Column I in my troubleshooting.

Here is the snip of code that I know is causing the issue, with the problem rows likely being the three rows below my IF formula.

Sheets("Raw_Data").Select
ActiveCell.Offset(0, 8).Columns("A:A").EntireColumn.Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Offset(4, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "MEASURE OUTCOME"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-3]=""MET"",RC[-2]=""MET"",RC[-1]=""MET""),""Met"",""Not Met"")"
ActiveCell.Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:A366")
ActiveCell.Range("A1:A366").Select
Selection.FormatConditions.Add Type:=xlTextString, String:="Met", _

Are you able to help me re-write this code to have the Macro fill the IF formula down to the last row of data rather than just the last cell in my current selection? The number of rows will change on a daily basis.

Thank you!

[#11]

@Kai El Harrar 

Hi

If you wee to create a Table instead, Insert > Table, then your formula would automatically be inserted as you add rows to the Table, without the need for any VBA code.

[#12]

@Steven1835

Hi,

Sorry about the late reply!

I think you need this one:

Sub FillFormulaDown()
'To check if the column is already exist
   If Application.WorksheetFunction.IsFormula(Range("J5").Offset(1, 0)) Then
     'skip
   Else
     Columns("J:J").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
     Range("J5").Value = "MEASURE OUTCOME"
   End If

     Range("J5").Offset(1, 0).Activate
     ActiveCell.FormulaR1C1 = "=IF(AND(RC[-3]=""MET"",RC[-2]=""MET"",RC[-1]=""MET""),""Met"",""Not Met"")"
     Selection.AutoFill Destination:=Range("J6:J" & Range("I" & Rows.Count).End(xlUp).Row)
     ActiveCell.EntireColumn.AutoFit
End Sub

You will find it in the attached workbook.

Please test it and tell me what you thought.

Hope that helps

[#13]

@Haytham Amairah 

Hi Haytham,

I have used your code in my macro when it imports from a text file and reorganizes the records. As part of it, it is supposed to copy down rows of a table as below:

ActiveCell.FormulaR1C1 = "=R[-1]C"
Range("A3").Select
Selection.AutoFill Destination:=Range("A3:A" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
Range("B3").Select
Selection.AutoFill Destination:=Range("B3:B" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select

This works nicely but if there is a single line of record in the file, it copies the row but then it adds a second row, copies down the first row in column A and B then returns a VB Error as shown in the attached screenshot.

Any ideas how to deal with it?

Thanks

Nick

[#14]

@NickNick33 

 Hi,

To figure out the problem, please provide a sample of the data you work on.

Regards

[#15]

@Haytham Amairah 

Sure, it is attached. Th eoriginal file is a tsv file but the upload tool would not accept tsv so I had to change it to csv. You will need to change it to tsv for the Excel file to read it. 

Thank you 

[#16]

@NickNick33

Hi,

This is what I got after opening your file:

Sorry, but the data isn't clear and I'm not sure what you're trying to do!

Based on your macro, you want to fill down Column A & B based on the number of rows in column E ??

[#17]

@Haytham Amairah 

Here are the macro steps:

• Imports the source file I provided,
• adds 2 columns (Shipmentid and Name) in addition to the columns in the source file.
• It takes what is in B1 in the source as Shipmentid and copies down the new column A
• It takes what is in B2, copies down the new column B
• Deletes the top rows up to the headings so that the whole file becomes one uniform table. 

This works fine except when there is a single in the source. I have attached both the Excel file and the different files that are used as the source. 

The macro is created to import the file called current.tsv. We just swap the files we want to import by renaming them "current.tsv". So I provided one sith single line and another with multiple. The one with multiple works fine but single one returns error as the VBA seems to have a problem with it. 

Curious situation. I hope this helps. 

[#18]

@NickNick33

Hi,

After I reproduced this issue, I noticed that the error also occurs even if you have two records in the imported file.

If you have one or two rows in the imported file, there is no need to use the AutoFill method in the macro because all rows in the table are already filled.

If you use it in this case, there will be an error!

The solution is to make the code smart somehow to see if the imported file has less than three rows and if so, skip the autofill process to prevent the error.

This is what I suggest to overcome this issue:

Sub PrepFile()
'
' PrepFile Macro
'

'
Sheets("Sheet1").Select
Range("A1").Select
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=current;Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [current]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "current"
.Refresh BackgroundQuery:=False
End With
Range("K10").Select
ActiveCell.FormulaR1C1 = "Shipment ID"
Range("L10").Select
ActiveCell.FormulaR1C1 = "Shipment Name"
Range("B2").Select
Selection.Copy
Range("K11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B3").Select
Application.CutCopyMode = False
Selection.Copy
Range("L11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Rows("10:10").Select
Application.CutCopyMode = False
Selection.Copy
Rows("1:1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Rows("2:10").Select
Range("A10").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Columns("K:L").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight

If Application.WorksheetFunction.CountA(Range("E:E")) - 1 = 1 Then
Range("A1").Select
Exit Sub
ElseIf Application.WorksheetFunction.CountA(Range("E:E")) - 1 = 2 Then
Range("A3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C"
Range("B3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C"
Range("A1").Select
Exit Sub
End If

Range("A3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C"
Range("B3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C"
Range("A3").Select
Selection.AutoFill Destination:=Range("A3:A" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
Range("B3").Select
Selection.AutoFill Destination:=Range("B3:B" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
Range("A1").Select
End Sub

Please replace your old macro with it.

Hope that helps

[#19]

@Haytham Amairah 

What do I have to add if the source isn't in the same worksheet?

Thanks

[#20]

@To31416

What's the source?
Please explain the problem well!

[#21]

@Haytham Amairah

Thanks for your quick reply!

I have an Excel file in which I import various reports and in this file I have a sheet, where I use those data for my formulas.

The various reports I import have the same length but differ in length every day.

In my main sheet where I use the imported data the first row always stays the same with all kind of formulas. However, I want my Macro to autofill the other rows until there is no more data in one of the imported reports.

Right now I have to set the autofill range manually. So if the report is too long, it's either cut or if it's too short I get errors.

Hope that was understandable.

Kind regards

[#22]

@To31416

It seems that this situation is different!

But I think you need to somehow get the total number of rows in the imported reports and then use that number in the macro.

If you can attach your file or a sample of it, this is would be helpful!

[#23]

@Haytham Amairah 

Thank you for helping me out with this.

I removed all data and a settings sheet but the important data is still there.

The macro-button is in the sheet "Output" Cell "F1"

The length should be the same as I got days in the reports. The totals column excluded.

[#24]

@To31416

Hi,

I've updated the code behind the Load button to fill down the rows based on the number of rows in (yield) sheet.

This is the code:

Sub FillRowsDown()
    'Get the last row in sheet (yield) and save it in the lastRow variable
    Dim lastRow As Long
    lastRow = Sheets("yield").Range("A" & Rows.Count).End(xlUp).Row
   
    'Subtract the first 6 rows from the lastRow
    lastRow = lastRow - 6
    
    'Subtract the Total row (if exists)
    If LCase(Sheets("yield").Range("A" & Rows.Count).End(xlUp).Value) = "total" Then
        lastRow = lastRow - 1
    End If
    
    With Sheets("output")
     .Range("A4:U4").AutoFill Destination:=Range("A4:U" & lastRow + 3)
     .Range("D4").Select
    End With
End Sub

Hope that helps

[#25]

@Haytham Amairah 

thank you so much!

you really were of great help

[#26]

@Haytham Amairah 

Hi Haytham,

If I use to populate Row H to length of Row E

Sub Test()

   Range("H2").Select
    
    Selection.AutoFill Destination:=Range("H2:H" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
End Sub

What would I need to add/change to get content from single Cell A2 and fill down from next empty in H

(the content of A2  changes and would like to see all changes updated in H rather than just overwriting) 

[#27]

@aimeelou

Hi,

If I understand you well, nothing has to change in the code!

You need to use the below formula in cell H2 to link this cell to cell A2.

=A2

After then, run the code so that you fill this formula down until the last row in column E and make the column H and A linked together.

Regards

[#28]

@Haytham Amairah 

Thanks for your help Haytham,

unfortunately I am probably doing something wrong as when I link the Cells then run macro rather than

filling down with the text that is in A2 - cells in H fill down with a zero

[#29]

@aimeelou

Could you attach a sample of the data you working on?

[#30]

@Haytham Amairah 

Thank you so much for helping

[#31]

@aimeelou

If you need to fill the column H with the content of only cell A2 so that each cell in column H will be filled with the same text in cell A2.

Then you just need to lock the formula in cell H2 as follows:

=$A$2

[#32]

@Haytham Amairah 

Hi Amairah,

I have a question somewhat similar to the post above.

Currently, I want to create a column with a function and want to autofill the column with the function until the end of the row

However, I wish to put in the number to rows so that it works on any worksheet (maybe with different number of rows)

Screenshot below is the formula I want to put into the column and autofill until the end of the row for any worksheet.

Also, this is what I have so far 

Sub MacroCode_Column fill

Dim myFirstColumn As Integer
Dim myLastColumn As Integer
Dim myFirstFieldRow As Integer
Dim myFirstDataRow As Integer
Dim myLastDataRow As Integer

myFirstFieldRow = 5
myFirstDataColumn = Cells(5, Columns.Count).End(xlToLeft).Column + 1
myLastDataRow = Cells(Rows.Count, 1).End(xlUp).Row

'Multiple Occurred

ActiveSheet.Cells(myFirstFieldRow, myFirstDataColumn + 6).Select
ActiveCell.Formula = "MCO Incurred"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=SUMIF(R6C5:R1321C5,RC5,R6C22:R1321C22)*RC31"
Selection.AutoFill Destination:=Range(Cells(myFirstFieldRow + 1, myFirstDataColumn + 6), Cells(myLastDataRow, myFirstDataColumn + 6))

Thank you in advance!!

[#33]

@D_Jang3884

Hi,

Based on what you want to fill the formula down?

You see that the solutions here in this conversation focus on filling the rows in a column based on the number of rows of another column.

[#34]

@Haytham Amairah 

Hi Amairah,

Sorry if I asked a non-related question to the post but regarding to your question - basically I want to update the number of rows to the formula below using the following variables (which I created to set first row to be 5 and the last row to be determined based on data I'm using)

Dim myFirstDataRow As Integer
Dim myLastDataRow As Integer

myFirstFieldRow = 5

myLastDataRow = Cells(Rows.Count, 1).End(xlUp).Row 

and the formula I want to update is below

ActiveCell.FormulaR1C1 = "=SUMIF(R6C5:R1321C5,RC5,R6C22:R1321C22)*RC31"

Currently, the rows in the formula is in numbers but I wish to replace it using dimensions.

I would greatly appreciate it if you could help or direct me to where I can ask.

Thanks!!

[#35]

@D_Jang3884

Hi,

I suggest you post this question in a separate post by starting a new conversation in here.

If you didn't find the answer, you may ask this community instead under [vba] tag.

Regards

[#36]

Hi @Haytham, I'm trying to do the same as above to autofill multiple columns to varying rows and I have tried a couple of different things I've seen above, but I fear I don't have the excel coding knowledge to know what I'm doing wrong. the following is my macro and I'd like the "E145040" to be variable. I also dont know if I need to change the "$A$915118", "$C$915118", "$E$915118", "$G$915118" and "$AC$145040" as well. Thank you so much in advance! Sub Macro5() ' ' Macro5 Macro ' ' Range("Y1").Select ActiveCell.FormulaR1C1 = "COMP" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = _ "=IF(RC[-21]=""Closed"",1,IF(RC[-21]=""Cancelled"",1,0))" ActiveCell.Offset(-1, 1).Range("A1").Select ActiveCell.Select ActiveCell.FormulaR1C1 = "PROC" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = _ "=IF(OR(RC[-22]=""Closed"",RC[-22]=""Verified"",RC[-22]=""Ready_To_Verify"",RC[-22]=""Cancelled""),1,IF(OR(RC[-22]=""Assigned"",RC[-22]=""Reassigned"",RC[-22]=""Pending""),IF(COUNTIF(RC[-11]:RC[-8],""Yes/Oui"")>0,1,0),0))" ActiveCell.Offset(-1, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "PC" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = _ "=IF(OR(RC[-23]=""Reassigned"",RC[-23]=""Assigned"",RC[-23]=""Pending""),IF(COUNTIF(RC[-12]:RC[-9],""Yes/Oui"")>0,1,0),0)" ActiveCell.Offset(-1, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "VER" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = _ "=IF(RC[-24]=""Verified"",1,IF(RC[-24]=""Ready_To_Verify"",1,0))" ActiveCell.Offset(-1, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "UNPROC" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = _ "=IF(OR(RC[-25]=""Closed"",RC[-25]=""Verified"",RC[-25]=""Ready_To_Verify"",RC[-25]=""Cancelled""),0,IF(OR(RC[-25]=""Assigned"",RC[-25]=""Pending""),IF(COUNTIF(RC[-14]:RC[-11],""Yes/Oui"")>0,0,1),1))" ActiveCell.Offset(0, -4).Range("A1:E1").Select Selection.AutoFill Destination:=ActiveCell.Range("A1:E145040") ActiveCell.Range("A1:E145040").Select Sheets.Add After:=ActiveSheet Sheets("Sheet1").Select Rows("1:1").Select Selection.AutoFilter ActiveSheet.Range("$A$1:$AC$145040").AutoFilter Field:=25, Criteria1:="0" Columns("J:J").Select Selection.Copy Sheets("Sheet2").Select Range("A1").Select ActiveSheet.Paste Sheets("Sheet1").Select Application.CutCopyMode = False ActiveSheet.ShowAllData Range("Z2").Select ActiveSheet.Range("$A$1:$AC$145040").AutoFilter Field:=26, Criteria1:="0" Columns("J:J").Select Selection.Copy Sheets("Sheet2").Select Range("C1").Select ActiveSheet.Paste Sheets("Sheet1").Select Application.CutCopyMode = False ActiveSheet.ShowAllData Range("M2").Select ActiveSheet.Range("$A$1:$AC$145040").AutoFilter Field:=27, Criteria1:="1" Columns("J:J").Select Selection.Copy Sheets("Sheet2").Select Range("E1").Select ActiveSheet.Paste Sheets("Sheet1").Select Application.CutCopyMode = False ActiveSheet.ShowAllData Range("M2").Select ActiveSheet.Range("$A$1:$AC$145040").AutoFilter Field:=28, Criteria1:="1" Columns("J:J").Select Selection.Copy Sheets("Sheet2").Select Range("G1").Select ActiveSheet.Paste Columns("A:A").Select Application.CutCopyMode = False ActiveSheet.Range("$A$1:$A$915118").RemoveDuplicates Columns:=1, Header:= _ xlNo Columns("C:C").Select ActiveSheet.Range("$C$1:$C$915118").RemoveDuplicates Columns:=1, Header:= _ xlNo Columns("E:E").Select ActiveSheet.Range("$E$1:$E$915118").RemoveDuplicates Columns:=1, Header:= _ xlNo Columns("G:G").Select ActiveSheet.Range("$G$1:$G$915118").RemoveDuplicates Columns:=1, Header:= _ xlNo Sheets("Sheet1").Select ActiveSheet.ShowAllData Sheets.Add After:=ActiveSheet Sheets("Sheet1").Select ActiveSheet.Range("$A$1:$AC$145040").AutoFilter Field:=9, Criteria1:= _ "PA Exceptions – Exception PA" Cells.Select Range("J1").Activate Selection.Copy Sheets("Sheet3").Select Range("A1").Select ActiveSheet.Paste Application.CutCopyMode = False Sheets.Add ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ "Sheet3!R1C1:R981068C29", Version:=xlPivotTableVersion15).CreatePivotTable _ TableDestination:="Sheet4!R3C1", TableName:="PivotTable3", DefaultVersion _ :=xlPivotTableVersion15 Sheets("Sheet4").Select Cells(3, 1).Select With ActiveSheet.PivotTables("PivotTable3").PivotFields("User ID") .Orientation = xlRowField .Position = 1 End With ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _ "PivotTable3").PivotFields("PROC"), "Sum of PROC", xlSum ActiveWindow.SmallScroll Down:=-84 Sheets("Sheet1").Select ActiveSheet.ShowAllData Sheets("Sheet1").Select Sheets("Sheet1").Name = "1.0 PA RAW" Sheets("Sheet4").Select Sheets("Sheet4").Name = "2.1 production" Sheets("Sheet3").Select Sheets("Sheet3").Name = "2.0 PA exceptions" Sheets("Sheet2").Select Sheets("Sheet2").Name = "1.1 Daily update" End Sub

[#37]

@Haytham Amairah 

I tried your suggestion and it works fine, but not for cases where there is only 1 row of data or no data (only heading). I have a heading in row 1, formula in row 2 (cell E2) which I'm trying to copy to the end of the table using this code:

Selection.AutoFill Destination:=Range("E2:E" & Range("C" & Rows.Count).End(xlUp).Row)

Many thanks in advance for your advice.

Sarka

[#38]

@jahoda

Hi,

Sorry for the late reply!

Please attach a sample of the data you're working to fit the code on it.

Regards

[#39]

Hi Haitham

I am new to VBA and I am trying to amend the ActiveCell.End(xlDown) part so that the formula (in column A) stops at the last row that contains data in column A. and again starts to autofill from this column to the next contains data and goes on,

and thank you in advance.

 this is a screenshot:

Sub autofill()

Dim Wad As String
Wad = Cells(Rows.Count, "A").End(xlUp).Row
Range("A30").Select
Selection.autofill Destination:=Range("A30:A" & Range("A" & Rows.Count).End(xlDown).Row)
Range(Selection, Selection.End(xlUp)).Select

Selection.autofill Destination:=Range("wad:A" & Range("A" & Rows.Count).End(xlDown).Row)
Range(Selection, Selection.End(xlUp)).Select
End Sub

Waddah

@Haytham Amairah 

[#40]

@walshamasi

Hi Waddah,

Sorry for the late reply, but I need some clarifications as column A seems to have no formulas in it!

If you can attach the worksheet here, that would be better.

Regards