VBA Excel: Extend a formula to the last row

Solved

Alice -
PhilDk Posted messages 20 Status Membre - 18 Aug 2014 at 10:43

Hello,

I'm simply looking for a VBA code to automatically extend a formula from cell C6 to the last cell in the row without specifying it.
Currently, the following code works very well:

Range("C6").Select

Selection.Autofill Destination:=Range("C6:C450")

But since the last cell is not always the 450th, I would like it to be defined automatically and on a case-by-case basis.

Thank you!

Configuration: Windows XP / Firefox 10.0.11

Liens connexes:

4 réponses

Réponse 1 / 4

pijaku Posted messages 13513 Registration date Status Modérateur Last intervention 2 771

Sure.
So now we just need to replace in

Destination:=Range("C6:C450")

the 450 with the variable DernLigne.
Let's take the opportunity to remove those unnecessary .Select and Selection...
For example:

Dim DernLigne As Long DernLigne = Range("C" & Rows.Count).End(xlUp).Row Range("C6").AutoFill Destination:=Range("C6:C" & DernLigne)

However, previously, we have been looking for the last non-empty row in column C. Now, in column C, we are trying to fill in with a formula... Therefore, for now, column C contains nothing, if I'm not mistaken... So it doesn't make sense to look for the last non-empty row in column C.

--
Best regards,
Franck

Alice

To clarify everything, I entered the following code:

Sub Extension_formule()

Dim DernLigne As Long

DernLigne = Range("C" & Rows.Count).End(xlUp).Row

Range("C6").AutoFill Destination:=Range("C6:C" & DernLigne)

End Sub

An error message came out, stating: "the Autofill method of the Range class failed".

Regarding your subsequent remark, I thought that by responding 450 to your question about the number displayed in the dialog box when entering the suggested code at the beginning of the forum, namely:

Sub Test()
Dim DernLigne As Long
DernLigne = Range("C" & Rows.Count).End(xlUp).Row
MsgBox DernLigne
End Sub

you would easily deduce that the famous column C is in a table of 450 rows. That’s actually the whole point of extending a formula...

pijaku Posted messages 13513 Registration date Status Modérateur Last intervention 2 771

Hello,

Actually, if you want to extend your formula from row 6 to row 450, it's likely that your column C is empty, to begin with.
Or you have data (or formulas) that you want to replace.

In the first case (empty column C), you need to calculate the last row based on another column. For example, column B may be filled.
In this case, the code should be as follows:

Sub Extension_formule() Dim DernLigne As Long DernLigne = Range("B" & Rows.Count).End(xlUp).Row Range("C6").AutoFill Destination:=Range("C6:C" & DernLigne) End Sub

In the second case, if your column C is already filled, the code given above is supposed to work.
Unless something is preventing it…
But here, it's a peculiar aspect of your workbook that we cannot know without seeing it…

I have tested and was able to reproduce the error "the Autofill method of the Range class failed" when column C only contains a formula in C6 and nothing else…

Try again with the last row calculation of another column and, if it still doesn't work, send us an example workbook via cjoint.com.

Réponse 2 / 4

pijaku Posted messages 13513 Registration date Status Modérateur Last intervention 2 771

Here is a code that should work, ADAPT IT, read the comments carefully...

Sub Sans_score11() Dim i As Integer, drLig As Long Application.ScreenUpdating = False 'loop from 4 to the number of sheets in the workbook For i = 4 To Sheets.Count 'in sheet number i With Sheets(i) 'insert a column between B and C .Columns("C:C").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove 'write "Sans score" in C5 .Range("C5").Value = "Sans score" 'Insert the formula in C6 .Range("C6").FormulaR1C1 = "=COUNTIF(C[9],""*score*"")" '******************************************************************************************* 'Here, you must determine which column allows you to find 'the last row. 'Column C, newly created, ends at C6. So it's not valid 'Column B? That, it's up to you to see and replace in the next line: '******************************************************************************************* drLig = .Range("B" & Rows.Count).End(xlUp).Row 'if drLig is less than or equal to 6 the autofill method will fail 'so we test drLig If drLig <= 6 Then 'if drLig is less than or equal to 6, message to the user: MsgBox "The last non-empty row of this column (sheet " & i & ") is less than or equal to 6. Please check" 'select the problematic sheet Sheets(i).Select 'stop and exit the procedure Exit Sub Else 'ELSE apply autofill .Range("C6").AutoFill Destination:=.Range("C6:C" & drLig) End If End With Next i Application.ScreenUpdating = True End Sub

--
Best regards,
Franck

Alice

Thank you for the code, which works wonderfully!

And if you want to take on new challenges of this type, I have a few others (of the same level I think)!

Thanks again!

pijaku Posted messages 13513 Registration date Status Modérateur Last intervention 2 771

And if you want to take on new challenges of this kind, I have a few more (of the same level, I think)!

It depends. Is it for school?

Alice

No, I am not a student, and even less in computer science; otherwise, I wouldn't have such difficulties.
It's simply to spare myself tedious tasks by wisely using the tools created for this purpose. But for that, knowledge is required that I am trying to acquire; in the meantime, I come to this forum.

pijaku Posted messages 13513 Registration date Status Modérateur Last intervention 2 771

It takes knowledge that I am trying to acquire,
That's to your credit...
in the meantime, I come to this forum.
Well, keep it up, you are welcome!

A+ don't hesitate at all...

Réponse 3 / 4

dlt-pan Posted messages 529 Status Membre 67

Hello
try:
Range("C6").Select
nbligne = Range("C65500").End(xlUp).Row
Selection.Autofill Destination:=Range(cells(6,3),cells(nbligne,3))

Alice

Hello,

Thank you for the response.
But when I enter the code, the following error message 1004 appears: "application-defined or object-defined error". Any idea?

Alice

"The Autofill method of the range class has failed"

Réponse 4 / 4

pijaku Posted messages 13513 Registration date Status Modérateur Last intervention 2 771

Hello,

To find the last non-empty row of a column using VBA, you can take a look at this CCM tip...

--
Best regards,
Franck

Alice

Hello,

Thank you for the link, but it's a bit Chinese to me.
I just want to extend my formula from cell C6 to the last cell in my table, which is always in column C.

Thank you

pijaku Posted messages 13513 Registration date Status Modérateur Last intervention 2 771

Have you at least taken the time to :
- read the tip?
- test it?
.........
First, try this in your workbook:

Sub Test() Dim DernLigne As Long DernLigne = Range("C" & Rows.Count).End(xlUp).Row MsgBox DernLigne End Sub

Alice

I did take the time to read and test it, but as a novice in VBA, I couldn't adapt it to my case.
Thank you for your response, but I don't see where cell C6 is, from which I want to extend my formula; did you take the time to read the question?

Thank you

pijaku Posted messages 13513 Registration date Status Modérateur Last intervention 2 771

Of course I read your question, otherwise I wouldn't be responding.
I don't want to give you the "ready-made" solution because it wouldn't be of any use to you...
C6 is not important in the story because it is a cell we know.
What we want to know is the number of the last non-empty row in column C; so we can extend our formula to it.
Right?
So, try the test macro I provided above, it should give you what you need.
We will adapt it to autofill after your tests.

Alice

The macro displays the number of the last line, in other words, the 450 mentioned above that I would like, if possible, not to have to enter every time I run the macro.

Thank you

VBA Excel: Extend a formula to the last row

4 réponses

Texture issues in enshrouded

Big fish games are they worth it?

Saracroche: the effective french app against phone spam

How to get the character µ on the android keyboard.

Format my packard bell pc

Why won't my computer recognize my headphones anymore?

Hibernate command

Access issue to my "contact me" profile

I can't answer the quiz.

How to disable the sound "beep" on my keyboard?