VBA Fill Right Formula for Selected Range to Last Column

Finalfight40

Active Member
Joined
Apr 24, 2018
Messages
273
Office Version
  1. 365
Platform
  1. Windows
Hi All

I have currently been working on a Macro but i'm having trouble with the section i have highlighted below. The section i'm struggling with has been copy and pasted and adapted from a previous Mr Excel thread but it was from 2012. (Please see link below)

HTML:
https://www.mrexcel.com/forum/excel-questions/614828-vba-how-autofill-left-right.html

Currently it will copy fill from cell B3 to the right but i would like it to take into account the small line of code below and fill the selected area to the right.

Code:
Range("B3", Range("B" & Rows.Count).End(xlUp)).Select

Any help or advice here is appreciated.

Code:
Sub SheetNames()


    Sheets("Overtime Calculator Template").Select
    Application.CutCopyMode = False
    Sheets("Overtime Calculator Template").Copy After:=Sheets(13)
    Sheets("Overtime Calculator Templat (2)").Select
    Sheets("Overtime Calculator Templat (2)").Name = "Overtime Calculator"
    Range("C4").Select
Sheets("Overtime Calculator").Select
For i = 1 To Sheets.Count - 2
    Cells(2, i + 1) = Sheets(i).Name
Next i
    Range("A3").Select
    Sheets(1).Select
    Range("A3", Range("A" & Rows.Count).End(xlUp)).Copy
    Sheets("Overtime Calculator").Select
    ActiveSheet.Paste
    Range("B3").Select
' From https://excelsemipro.com/2011/09/fill-down-a-formula-with-vba/
    Dim rng As Range
    Dim rngData As Range
    Dim rngFormula As Range
    Dim rowData As Long
    Dim colData As Long
' Set the ranges
    Set rng = ActiveCell
    Set rngData = rng.CurrentRegion
' Set the row and column variables
    rowData = rngData.CurrentRegion.Rows.Count
    colData = rng.Column
' Set the formula range and fill down the formula
    Set rngFormula = rngData.Offset(1, colData - 1).Resize(rowData - 1, 1)
    rngFormula.FillDown
[B]    Range("B3", Range("B" & Rows.Count).End(xlUp)).Select[/B]
[B]    Dim lastcolumn As Long[/B]
[B]    lastcolumn = Cells(2, Columns.Count).End(xlToLeft).Column[/B]
[B]    Range("B3").AutoFill Destination:=Range(Cells(3, 2), Cells(3, lastcolumn))[/B]


    End Sub
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
How about
Code:
Dim lastcolumn As Long
Dim lastRow As Long
   lastRow = Range("B" & Rows.Count).End(xlUp).Row
    lastcolumn = Cells(2, Columns.Count).End(xlToLeft).Column
   Range("B3:B" & lastRow).AutoFill Range("B3", Cells(lastRow, lastcolumn))
 
Upvote 0
How about
Code:
Dim lastcolumn As Long
Dim lastRow As Long
   lastRow = Range("B" & Rows.Count).End(xlUp).Row
    lastcolumn = Cells(2, Columns.Count).End(xlToLeft).Column
   Range("B3:B" & lastRow).AutoFill Range("B3", Cells(lastRow, lastcolumn))

Fluff, you sir are a legend.

Thank you for this, i've been trying to play around with the previous code to try to get it to work for a couple of hours.

Works perfectly.
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,296
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top