Dynamic Autofill in Macro

[yagu99]

I'm wondering if there is a way to perform a dynamic autofill & stop the autofill when it comes to a blank cell. I need the autofill to use the exact data within the other cells of the range.

I currently have the autofill where I specify the range (in this case it would be F1 - F26):

Range("F1").AutoFill Destination:=Range("F1:F26"), Type:=xlFillDefault


I have multiple spreadsheets that I need to perform an autofill but, they are not the same range.

For example,
One Spreadsheet may have a range of F1 - F26, another spreadsheet's range could be F1 - F8, another spreadsheet's range could be F1-F18, etc.. . They all have an empty cell at the end of the range so, I was thinking I could use this in helping me specify the range of each spreadsheet.

Thanks,
yagu99

 

[Trevor G]

Welcome to the board.

Take a look at this sample. It is applying a copy and paste to each cell, I am using the first 3 sheets

Sub fillTillBlank()
Dim i As Integer
For i = 1 To 3
Worksheets(i).Activate
Range("E2").Copy
Range("D2").Activate
Do Until ActiveCell.Value = ""
ActiveCell.Offset(0, 1).PasteSpecial xlPasteAll
ActiveCell.Offset(1, -1).Select
Loop

Next i
Application.CutCopyMode = False
End Sub

 

[yagu99]

Trevor,

Thanks for the assistance. I was able to tweak the code for what I need so, it got me on the right path.

Thanks Again,
yagu99

 

[Peter_SSs]

I was able to tweak the code for what I need so, it got me on the right path.

Looping through row by row and selecting/activating cells are both not efficient ways to do a job like this. If your ranges are not big it won't really matter that much but if you would like to learn some more efficient code to do this, post the code that you ended up using. If posting code, please use Code Tags so that your code gets posted with any indentation formatting you have. See my signature block for how to use Code Tags.

 

[brubru24]

Hi everyone,

I am new to VBA and trying to learn slowly.

I would like to have an autofill function dynamic in my macro.

I basically have a report which, could increase or decrease in terms of raws but not column.

My autofill function would have to recognise this.

Anyone knows the code to use?

Thank you,

Bruno

 

[yagu99]

If there is a more efficient way to do this, I'm interested in learning it.

Here is the code I went with (that works for now, unless I can find a more efficient way to do it):

Sub test()
Worksheets("sheet1").Activate
Range("D1").Copy
Range("D2").Activate
Do Until ActiveCell.Value = ""
ActiveCell.Offset(0, 0).PasteSpecial xlPasteAll
ActiveCell.Offset(1, 0).Select
Loop<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>

Application.CutCopyMode = False<o:p></o:p>
End Sub<o:p></o:p>

 

[JoeMo]

If there is a more efficient way to do this, I'm interested in learning it.

Here is the code I went with (that works for now, unless I can find a more efficient way to do it):

Sub test()
Worksheets("sheet1").Activate
Range("D1").Copy
Range("D2").Activate
Do Until ActiveCell.Value = ""
ActiveCell.Offset(0, 0).PasteSpecial xlPasteAll
ActiveCell.Offset(1, 0).Select
Loop<o:p></o:p>

Application.CutCopyMode = False<o:p></o:p>
End Sub<o:p></o:p>

Here's one way that's more efficient:

Sub test()
Dim rng As Range
Worksheets("sheet1").Activate
Range("D1").Copy
If IsEmpty(Range("D2")) Then
    Application.CutCopyMode = False
    MsgBox "First cell for paste is empty - goodbye."
    Exit Sub
End If
Set rng = Range(Range("D1"), Range("D1").End(xlDown))
For Each c In rng
    c.PasteSpecial xlPasteAll
Next c
Application.CutCopyMode = False
End Sub

 

[Peter_SSs]

If there is a more efficient way to do this, I'm interested in learning it.

Here is the code I went with (that works for now, unless I can find a more efficient way to do it):

Sub test()
Worksheets("sheet1").Activate
Range("D1").Copy
Range("D2").Activate
Do Until ActiveCell.Value = ""
ActiveCell.Offset(0, 0).PasteSpecial xlPasteAll
ActiveCell.Offset(1, 0).Select
Loop<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>

Application.CutCopyMode = False<o:p></o:p>
End Sub<o:p></o:p>

This appears to be over-writing existing values in D2:D?? with the D1 value. Is that the intention? If so, try this. Note that there is no 'selecting' and no looping.

Sub test()
    With Worksheets("sheet1")
        .Range("D1").Copy Destination:=.Range("D1", .Range("D" & .Rows.Count).End(xlUp))
    End With
End Sub

If that is not what you want, please provide more details.

Also, indent your code in the Code Tags for easier reading/debugging.

 

[Trevor G]

Hi everyone,

I am new to VBA and trying to learn slowly.

I would like to have an autofill function dynamic in my macro.

I basically have a report which, could increase or decrease in terms of raws but not column.

My autofill function would have to recognise this.

Anyone knows the code to use?

Thank you,

Bruno

Welcome to the Forum,

What have you tried so far? what code do you have? You can record an autofitler with the mouse on the main sheet and that will give you the column information you will need to consider. Here is an example

Range("A1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$E$18").AutoFilter Field:=4, Criteria1:="=Milk", _
Operator:=xlAnd

 

[brubru24]

Hi Trevor,

Thank you for your reply.

Basically, my report has a set of prices with corresponding dates.

These dates could be expired and I would like to delete them from my report.

So far my code is this:


Sub Report_modification()

'Inserts today formula

Range("L3").Select
ActiveCell.FormulaR1C1 = "=TODAY()"

'inserts if formula to identify the age of sales price list

Range("J2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]<=R3C12,TRUE,FALSE)"

'drags the formula down ( I would like this to be dynamic so if it is more or less than J29197 the formula would only auto fil at that cell))


Range("J2").Select
Selection.AutoFill Destination:=Range("J2:J29197")
Range("J2:J29197").Select
Range("J1").Select
Selection.Style = "Normal 10"
ActiveCell.FormulaR1C1 = "True/False"

End Sub


Once the formula has been autofilled to the dynamic cell, I would filter it by true or false, delete the expired once and save the report.

I hope it all makes sense.

Many thanks for your help.

Bruno