[Excel] Autofill Method in a Macro
Solved
Arn's
Posted messages
25
Status
Member
-
pijaku Posted messages 13513 Registration date Status Moderator Last intervention -
pijaku Posted messages 13513 Registration date Status Moderator Last intervention -
Hello everyone,
In a macro, I want to use the Autofill method to automatically fill certain cells. The problem is that I do not know in advance how many cells to fill.
Let me explain:
When I use file1 (where cells A1 to A10 are filled) to record my macro, I fill cells B1 to B10 using the fill handle and I get among other things the following line:
But when I apply the macro to file2 where cells A1 to A15 are filled, the macro only fills cells B1 to B10.
Thank you in advance for your responses. I remain available for any further questions.
In a macro, I want to use the Autofill method to automatically fill certain cells. The problem is that I do not know in advance how many cells to fill.
Let me explain:
When I use file1 (where cells A1 to A10 are filled) to record my macro, I fill cells B1 to B10 using the fill handle and I get among other things the following line:
Selection.AutoFill Destination:=Range("B1:B10") But when I apply the macro to file2 where cells A1 to A15 are filled, the macro only fills cells B1 to B10.
Thank you in advance for your responses. I remain available for any further questions.
2 answers
-
Ah, I've got it. Just for your information, you simply need to calculate N=number of rows, and replace 10 with N:
Dim NbLignes NbLignes = ActiveSheet.UsedRange.Rows.Count Range("B1").Select Selection.AutoFill Destination:=Range("B1:B" & NbLignes), Type:=xlFillDefault Range("B1:B" & NbLignes).Select -
Hello,
You can use this:
Dim numlign As Integer
numlign = Sheets("Feuil1").Range("A65536").End(xlUp).Row ' returns the number of the last "non-empty" row in column A
Selection.AutoFill Destination:=Range("B1:B" & numlign)
--
Best regards,
-- Anything worth doing is worth doing well --
