Autofill vba excel

VBA AutoFill

What Does AutoFill Do in Excel VBA?

We have seen autofill in worksheet where the cells are automatically filled based on the values in the previous cells above it, we can use VBA so that excel does the task for us, to do this we use the Selection.Autofill method and we provide the destination i.e. up to which cells the values needs to be filled.

The best use of VBA autofill comes when we need to fill the formula of the first cell to the cell of the column. We usually apply the formula in the first cell either we copy & paste to the last cell or we just auto fill by double click on the little arrow key. Another best example of using autofill in excel is when we need to insert serial numbers. We usually type the first three numbers then we drag down till the required last cell.

In VBA too we can perform the task of AutoFill method. In this article, we will show you how we can use the autofill method and ways to write the code. Now we will see how we can use this tool in VBA coding.

How to Use AutoFill in VBA?

To use the autofill in VBA we need to understand the syntax of the auto fill method. Below is the syntax of the auto fill.

• Range (“A1”): What are the cells to identify the patter of the fill series.
• Destination: Till what cell you want to continue the fill series pattern. Here we need to mention the full range of cells.
• Type as xlAutoFillType: Here we can select the series fill type. Below are the list of items in this parameter – xlFillCopy, xlFillDays, xlFillDefault, xlFillFormats, xlFillMonths, xlFillSeries, xlFillValues, xlFillWeekdays, xlFillYears, xlFlashFill, xlGrowthTrend, xlLinearTrend.

Examples of AutoFill in Excel VBA

Let’s see some simple to advanced examples of VBA AutoFill in excel.

Example #1 – xlFillDefault

First, enter 3 serial numbers in the first three cells.

In the VBA sub procedure mention the VBA range as Range (“A1: A3”)

Code:

Now access the AutoFill method.

Enter the destination as Range (“A1: A10”)

Code:

Select the Type as xlFillDefault.

Code:

Now run the code we will get the serial numbers from 1 to 10.

Since we mentioned the end destination cell as A10 it has stopped there, we can enter the destination cell as the last cell of the excel.

Example #2 – xlFillCopy

For the same numbers, we will use the type as xlFillCopy.

Code:

I have the copy of the first three cells to the remaining cells.

Example #3 – xlFillMonths

For this example, I have entered the first three months in the first 3 cells.

Change the autofill type to xlFillMonths.

Code:

This will fill the month series.

Example #4 – xlFillFormats

For this example, I have entered numbers and applied formatting to those cells.

Now I will change the type to xlFillFormats.

Code:

Run this code and see what happens.

It has filled formats of the first three cells to the next three cells and again next three cells and so on.

Example #5 – xlFlashFill

For this example, I have entered a few values from cell A1 to A10 as shown in the below image.

From this list, I want to extract the numerical part. To tell excel about the pattern, in the first cell I will manually enter the numerical part of the first cell.

Now I will write the code as usual and change the type to xlFlashFill. This time we will use the B column range.

Code:

If I run this code we will get the result like the below.

This is the overview of the VBA AutoFill method. Hope you have enjoyed it.

You can download this VBA AutoFill Excel Template from here – VBA AutoFill Excel Template

Recommended Articles

This has been a guide to VBA AutoFill in excel. Here we discuss How to Use Autofill in Excel VBA with various parameters like xlFillDefault, xlFillFormats, xlFlashFill, etc. You can learn more about VBA from following articles –

Autofill Application.Countifs.Formula VBA Excel

I think this might be a simple question but I can’t seem to find a way to autofill an Application.WorksheetFunction.SumProduct(wsf.CountIf formula so that the row number would change as the formula goes down.

I want to autofill this formula for B2 till LastRow from Column A Application.WorksheetFunction.SumProduct(wsf.CountIfs(Sheet2.Range(**B2 Here**), Doctors, Sheet2.Range(**B2 Here**), wsf.Transpose(Emergency)))

So lets assume that the last used row for column A is 6, so the formula should autofill from B2 until B6 such as:

B3 = Application.WorksheetFunction.SumProduct(wsf.CountIfs(Sheet2.Range(**B3 Here**).

B4 = Application.WorksheetFunction.SumProduct(wsf.CountIfs(Sheet2.Range(**B4 Here**).

B5 = Application.WorksheetFunction.SumProduct(wsf.CountIfs(Sheet2.Range(**B5 Here**).

B6 = Application.WorksheetFunction.SumProduct(wsf.CountIfs(Sheet2.Range(**B6 Here**).

I don’t want to use .Formula «=SUMPRODUCT(COUNTIFS. » approach so that I can use arrays as the criteria vary according to the selected dropdown choices.

Создан 10 ноя. 13 2013-11-10 22:19:23 CaptainABC

2 ответа

AutoFill can do this for you. Assuming your formula begins in B2 and the end row changes, the syntax would be:

Another option would be using FillDown as AutoFill almost always have quirks. The syntax is pretty much the same:

Let us know if this helps.

UPDATE:

Much like Dave’s answer, here’s my take on your formula:

HOWEVER, here’s the clincher: If you are merely doing a COUNTIFS checking if Column C has Doctors as value and Column I has the value of B2 , you should remove the SUMPRODUCT from your formula as it’s not necessary. SUMPRODUCT was once the way to check multiple conditions, but in Excel 2007 and up, it’s been completely replaced by COUNTIFS for simple multiple condition-based check-and-count. What I’m saying is, the proper code is pretty much the following:

STILL, further formula forensics and evaluation reveal a trickier part: You are entering your formula in B2 and below, but your COUNTIFS is checking B2 and below as part of the condition as well. This is not only confusing but is also prone to error (if not already erroneous) as it will result into a circular reference.

At this point, my question now is, what exactly are you trying to check and/or count? This is more than just a VBA issue, in my opinion, as I believe that you are confused as to what and how you want to get your numbers. While our approach is sound and is basically perfect for your question, if the formula is incorrect, the above is useless. 🙂

Создан 11 ноя. 13 2013-11-11 02:21:25 Manhattan

Nice one! You can probably remove ‘Columns(«A»)’, as column «A» is also referenced in ‘Cells(Rows.Count,1)’ — eg ‘Sheet1.Cells(Rows.Count, 1).End(xlUp).Row’ – DaveU 11 ноя. 13 2013-11-11 03:40:59

Hey, Dave. Changed as per your comment. Thanks! – Manhattan 11 ноя. 13 2013-11-11 07:32:30

I have tried this code: ‘Dim wsf Set wsf = Application.WorksheetFunction LRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row Sheet1.Range(«B2»).Formula = «Application.WorksheetFunction.SumProduct(wsf.CountIfs(Sheet1.Range(«»C2:C16″»), Doctors, Sheet1.Range(«»I2:I16″»), Sheet1.Range(«»B2″»)))» Sheet1.Range(«B2:B» & LRow).FillDown’ It is not working I want B2 to change as the formula is filled down. What am I doing wrong? @DaveU Any Clues? – CaptainABC 11 ноя. 13 2013-11-11 20:30:41

‘Application.Worksheet. ‘ is not used ins > – Manhattan 12 ноя. 13 2013-11-12 02:14:18

@BK201, I find it interesting that someone else also finds the formula somewhat confusing. Question — I noticed that you removed the sheet references (‘.Name’) from the formula text. Is it because we’re assuming the formula is operating on the active sheet in this scenario? Just asking;-) – DaveU 12 ноя. 13 2013-11-12 03:11:23

@DaveU: You’re correct. 🙂 The above was actually tested with dummy data that mirrors the OP’s data somewhat, which is why I did not bother using ‘.Name’ for the time being. I was actually too concentrated on the VBA that I noticed the formula a little too late. It worked, but it prompted a circular reference, which I was apparently too careless to notice the first time around. Because of this, I decided to just investigate the formula and not the VBA, as our approaches have been perfect for the request anyway. 😉 – Manhattan 12 ноя. 13 2013-11-12 04:24:29

@DaveU Thanks for the above answer. However Doctors is actually a defined array: ‘Doctors = Array(«John», «Peter», «Tom»)’ Unfortunately when using the above it treats «Doctors» as a criteria rather than an array. Any way to fix that without merging <"John", "Peter", "Tom">into the formula as the Doctor array changes based on a Dropdown control. Thanks. – CaptainABC 15 ноя. 13 2013-11-15 16:16:44

@BK201 Any suggestions for the above comment? – CaptainABC 15 ноя. 13 2013-11-15 16:17:38

You’ll need to get the dropdown value into a string, and substitute that into the formula, instead of «Doctors» – DaveU 15 ноя. 13 2013-11-15 16:53:02

@DaveU Thank you for the prompt response. How do I need to go about getting the value into a string and substitute it? And will it work when for example the Dropdown value is «All» and the Doctors need to be «John» and «Peter» and «Tom» – CaptainABC 15 ноя. 13 2013-11-15 18:50:04

What sort of dropdown is it, a val will require a bit more effort, but possible – DaveU 15 ноя. 13 2013-11-15 20:21:19

@DaveU It’s a combobox. My main problem is using the «All» criteria. Also, when I try to substitute anything in place of «Doctors» it gives me 0 results. Any clues? – CaptainABC 15 ноя. 13 2013-11-15 21:27:07

I’m actually having a problem getting your formula to work ‘.Range(«B2»).Formula = «=COUNTIFS($C$2:$C$16, «»Doctors»» , $I$2:$I$16, $B2)»‘ Your 2nd criteria is the result of the formula, so I think ‘$B2’ should be something else. Not an expert on COUNTIFS, maybe @BK201 has some advice. – DaveU 15 ноя. 13 2013-11-15 23:59:08

@DaveU I have actually changed the formula so that ‘$B2’ is now ‘$A2’. But I still am having trouble to define the «Doctors» part and get it to work, – CaptainABC 16 ноя. 13 2013-11-16 00:12:21

I’m on mobile so I can’t post some code right now. However, form controls are linked to a cell, are they not? If so, it’s a matter of an ‘IF THEN’ where if combo box is equal to All, condition is equal to a wildcard to catch all the doctors’ names. Or am I getting something wrong here? – Manhattan 16 ноя. 13 2013-11-16 02:27:38

AutoFill

Use AutoFill in Excel to automatically fill a series of cells. This page contains many easy to follow AutoFill examples. The sky is the limit!

1. For example, enter the value 10 into cell A1 and the value 20 into cell A2.

2. Select cell A1 and cell A2 and drag the fill handle down. The fill handle is the little green box at the lower right of a selected cell or selected range of cells.

Note: AutoFill automatically fills in the numbers based on the pattern of the first two numbers.

3. Enter Jan into cell A1.

4. Select cell A1 and drag the fill handle down. AutoFill automatically fills in the month names.

5. Enter Product 1 into cell A1.

6. Select cell A1 and drag the fill handle down. AutoFill automatically fills in the product names.

7. Enter Friday into cell A1.

8. Select cell A1 and drag the fill handle down. AutoFill automatically fills in the day names.

9. Enter the date 1/14/2019 into cell A1.

10. Select cell A1 and drag the fill handle down. AutoFill automatically fills in the days.

11. Instead of filling in days, use the AutoFill options to fill in weekdays (ignoring weekend days), months (see example below) or years.

Note: also see the options to fill the formatting only and to fill a series without formatting.

12. Enter the date 1/14/2019 into cell A1 and the date 1/21/2019 into cell A2.

13. Select cell A1 and cell A2 and drag the fill handle down. AutoFill automatically fills in the dates based on the pattern of the first two dates.

14. Enter the time 6:00 AM into cell A1.

15. Select cell A1 and drag the fill handle across. AutoFill automatically fills in the times.