1

I am trying to auto fill down from the first cell that I have in cell B1 based on how many rows are in column A. Any I ideas on how to account for this. Thanks!

Set sourceRange = Worksheets("Sheet1").Range("A1:A20") 
Set fillRange = Worksheets("Sheet1").Range("B1:B20") 
sourceRange.AutoFill Destination:=fillRange

3 Answers3

2

Change your code to this:

Dim lr as Long
lr = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

Set sourceRange = WorkSheets("Sheet1").Range("A1:A" & lr)
Set fillRange = sourceRange.Resize(, sourceRange.Columns.Count + 1)
sourceRange.AutoFill Destination:=fillRange

EDIT:

Note, as it's unclear from your question, if you also wish to detect last column, then: 

Dim lc as Long
lc = Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column

will detect it for you, you can adjust your sourcerange, and set your fillRange to be the rightmost adjacent column

Set sourceRange = WorkSheets("Sheet1").Range(Cells(1,1), Cells(lr, lc))
Set fillRange = sourceRange.Resize(, sourceRange.Columns.Count + 1)
sourceRange.AutoFill Destination:=fillRange

will do the trick!

Samuel Hulla
  • 6,617
  • 7
  • 36
  • 70
  • I think OP is looking for total columns and not rows. Which is just a variant of your answer – Zac Jul 18 '18 at 13:43
  • It's kind of unclear. I thoguht so as well originally, but it kind of defeats the purpose if he directly references next column (B) as the fill range. – Samuel Hulla Jul 18 '18 at 13:45
  • @SJR yeah that's just oversight on my part, as I copied OP's code instead of transcribing it myself. Will edit in a sec – Samuel Hulla Jul 18 '18 at 13:47
  • @Zac edited my answor to accomodate last column being used as well. But I still find it kind of funky in practical use. – Samuel Hulla Jul 18 '18 at 14:00
  • Not sure what OP wants now because now I think they have changed it to look for number of rows.. atleast you've covered both :) – Zac Jul 18 '18 at 14:02
2

Here's a guess as not sure what you are trying to do.

Sub x()

Dim SourceRange As Range, fillRange As Range

With Worksheets("Sheet1")
    Set SourceRange = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
    Set fillRange = .Range("A1").Resize(SourceRange.Rows.Count, 2)
End With

SourceRange.AutoFill Destination:=fillRange

End Sub
SJR
  • 22,986
  • 6
  • 18
  • 26
0

The best way I found to circumvent this issue is as follows 

Range("B1").Select
Selection.Copy
ActiveCell.Offset(0, -1).Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Range(Selection, "B1").Select
ActiveSheet.Paste

Doesn't quite auto fill but should do the job for you.

TDJ
  • 121
  • 1
  • 14
  • Even better if you can do dispense with the Selects. – SJR Jul 18 '18 at 13:45
  • @SJR What do you mean dispense with the selects? I just tried this code and it worked for me. –  Jul 18 '18 at 13:48
  • https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?rq=1 – SJR Jul 18 '18 at 13:49
  • Are you meaning to set a range as opposed to using select? –  Jul 18 '18 at 13:53
  • @BRPOR In general it's best to avoid `.Select` altogether (causes a lot of unexpected behaviour and so on). The code will work, but it is teaching a bad habit. I really like the question SJR linked to, one of the latter answers pretty nicely explains not only how you should avoid it, but also why you should avoid it – Samuel Hulla Jul 18 '18 at 14:03