VBA Drag down cell content to end of data

[Kra]

Hi all,

I have some troubles with macro. I need to drag down formulas and fixed values from range O3:BB8 to the end of data in column N.

I tried to do it this way, but it's not working:

Public Function GetLastRow(Optional Col As Integer = 1, Optional Sheet As Excel.Worksheet)
    
    If Sheet Is Nothing Then
        Set Sheet = Application.ActiveSheet
        GetLastRow -Sheet.Cells(Sheet.Rows.Count, Col).End(xlUp).Row
    End If
 End Function

Sub DragFormulas()

 Dim LastRow As Long
 
    LastRow = GetLastRow(1, Worksheets("SUM"))
    
    Worksheets("SUM").Range("O3:BB8").AutoFill
     Destination:=Worksheets("SUM").Range("O3:BB" & LastRow), _
     Type:=xlFillDefault
    
End Sub

Any ideas how to achieve it?

 

[Fluff]

If you want to use col N to calculate the last row then it should be

LastRow = GetLastRow(14, Worksheets("SUM"))

 

[Kra]

If you want to use col N to calculate the last row then it should be

LastRow = GetLastRow(14, Worksheets("SUM"))

It is still not working, whole row is always empty, so column A and N are both blanks.

 

[Fluff]

If column N is blank, why did you say

I need to drag down formulas and fixed values from range O3:BB8 to the end of data in column N.

 

[Kra]

If column N is blank, why did you say

Sorry, I mean I have some data in columns A-N. Always the same length. So if data in column A ends, it means that it ends also in column N. I tried your code line and it is also not working.

 

[Fluff]

In that case what does "not working" actually mean?

 

[Alex Blakenburg]

Give this a try:
(both subs have changed)

Public Function GetLastRow(Optional Col As Integer = 1, Optional Sheet As Excel.Worksheet)

    If Sheet Is Nothing Then
        Set Sheet = Application.ActiveSheet
    End If
    GetLastRow = Sheet.Cells(Sheet.Rows.Count, Col).End(xlUp).Row
 End Function

Sub DragFormulas()

 Dim LastRow As Long
 
    LastRow = GetLastRow(1, Worksheets("SUM"))
    
    Worksheets("SUM").Range("O3:BB3").AutoFill _
        Destination:=Worksheets("SUM").Range("O3:BB" & LastRow), _
        Type:=xlFillDefault
    
End Sub

 

[Kra]

Give this a try:
(both subs have changed)

Public Function GetLastRow(Optional Col As Integer = 1, Optional Sheet As Excel.Worksheet)

    If Sheet Is Nothing Then
        Set Sheet = Application.ActiveSheet
    End If
    GetLastRow = Sheet.Cells(Sheet.Rows.Count, Col).End(xlUp).Row
 End Function

Sub DragFormulas()

 Dim LastRow As Long
 
    LastRow = GetLastRow(1, Worksheets("SUM"))
   
    Worksheets("SUM").Range("O3:BB3").AutoFill _
        Destination:=Worksheets("SUM").Range("O3:BB" & LastRow), _
        Type:=xlFillDefault
   
End Sub

Thank you, this one indeed drags the data down, but I have an issue with fixed values. For example in column AS I have fixed value "1000" in rows 1-8. Macro changes it to 1001, 1002, 1003... etc. is there any way to prevent it?

 

[Alex Blakenburg]

Thank you, this one indeed drags the data down, but I have an issue with fixed values. For example in column AS I have fixed value "1000" in rows 1-8. Macro changes it to 1001, 1002, 1003... etc. is there any way to prevent it?

Try changing

Type:=xlFillDefault

To:

Type:=xlFillCopy

 

[Kra]

Try changing

Type:=xlFillDefault

To:

Type:=xlFillCopy

Thank you! Works correctly now!