3

I am trying to do a AutoFill from the last row to one row below. So LastRow to LastRow Offset (1).

I know how to find the last row in VBA, but some how I can't find a way to solve this puzzle.

I think It will look something like the following:

LastRow = Cells(Rows.Count, 2).End(xlUp).Row
LastRow2 = Cells(Rows.Count, 2).End(xlUp).Offset(1)


Range("B" & LastRow : "AA" & LastRow).Select
Range("B" & LastRow2 : "AA" & LastRow2).Autofill

If something is not clear please ask, I would appreciate any help

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Peter
  • 65
  • 1
  • 12

4 Answers4

1

Add .Row to the end of your LastRow2 variable:

LastRow2 = Cells(Rows.Count, 2).End(xlUp).Offset(1).Row
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Mehmet Canbulat
  • 91
  • 2
  • 7
  • Hi Mehmet, thanks for the response. And you probably are correct. My problem however lies with - Range("B" & LastRow : "AA" & LastRow).Select - VBA does not recognise this command. – Peter Jan 11 '19 at 09:16
  • You can refer below answer for this and try to avoid select at vba as much as you can. – Mehmet Canbulat Jan 11 '19 at 09:18
  • @Peter you have fix your code as this-->> Range("B" & LastRow & ":AA" & LastRow).Select – Mehmet Canbulat Mar 28 '19 at 13:08
1

Add .Row to the end of your LastRow2 variable and use the Range.AutoFill method correctly: 

SourceRange.Autofill Destination:=DestinationRange

where the DestinationRange must include the SourceRange.

I highly recommend to avoid using Select.

LastRow = Cells(Rows.Count, 2).End(xlUp).Row

Range("B" & LastRow : "AA" & LastRow).Autofill Destination:=Range("B" & LastRow : "AA" & LastRow + 1)
                                                                        '^ Note this must be LastRow not LastRow + 1!

Alternative:

Dim LastCell As Range
Set LastCell = Cells(Rows.Count, 2).End(xlUp) 'without .Row

LastCell.Resize(1, 26).AutoFill Destination:=LastCell.Resize(2, 26) '1 down

Alternative:

Dim SourceRange As Range
Set SourceRange = Cells(Rows.Count, 2).End(xlUp).Resize(1, 26) 'results in column B to AA

SourceRange.AutoFill Destination:=SourceRange.Resize(RowSize:=2) '1 down
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • Hi Peh, I'm getting a run-time error (424) saying "Object Required" – Peter Jan 11 '19 at 09:23
  • @Peter corrected to `LastCell.Resize(1, 26).AutoFill Destination:=LastCell.Resize(1, 26).Offset(1)` see my edited answer – Pᴇʜ Jan 11 '19 at 09:26
  • It's now saying "AutoFill method of Range class failed". This error occurs on the last row of code. – Peter Jan 11 '19 at 09:33
  • @Peter Sorry my bad, obviously the destination range must include the source range. See my edited answer. Should work now, tested it. – Pᴇʜ Jan 11 '19 at 09:40
1

The destination range to AutoFill might be declared like this.

With ActiveSheet
    Set Rng = .Cells(.Rows.Count, "B").End(xlUp).Resize(2, 26)
End With
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Variatus
  • 14,293
  • 2
  • 14
  • 30
  • are you sure? [Documentation](https://learn.microsoft.com/en-us/office/vba/api/excel.range.autofill) says destination parameter in `AutoFill` is mandatory and not optional. – Pᴇʜ Jan 11 '19 at 09:23
  • This doesn't seem to work, I'm getting a run-time error saying - "Argument not optional" – Peter Jan 11 '19 at 09:29
  • @PEH No, I'm not sure. That's why I said "should work". Peter, sorry about that. I thought you knew how to do the AutoFill part. I was just trying to show how to set the range. I would expect to have to specify what to AutoFill. Are you sure your intention is to AutoFill? It seems like you might want to append a row at the end of your table which inherits the format from the last used row. That wouldn't be done using AutoFill. Use the Macro Recorder for an idea how to code that. – Variatus Jan 11 '19 at 09:46
0

Wanted to chime in on this as well. What looks to be the issue is how you have below constructed:

Incorrect:

Range("B" & LastRow : "AA" & LastRow).Select

The colon should be enclosed in quotes too.

Corrected: Should be formatted with the colon in the first string such as:

Range("B" & LastRow & ":" & "AA" & LastRow).Select
Russell
  • 1
  • 1