vba autofill / error range class

Rotating_Potato

New Member
Joined
Oct 9, 2015
Messages
8
Hello,

Desperately in the need of the advise of the forum's experts. I have a code:

Code:
Sub autofill()


Workbooks(file_main).Worksheets("sheet1").Activate
ActiveSheet.Range("R52").End(xlDown).Select
ActiveSheet.Range(Selection, Selection.End(xlToLeft)).Select
Selection.autofill Destination:=ActiveSheet.Range(Range("R52").End(xlUp).Offset(-1, 0), Range("HH52").End(xlDown)), Type:=xlFillDefault


End Sub

The above produces an error 1004 of range class failed. Tried multiple variations of the code, but in the essence followed same logic.

The goal that I want to achieve: find the last cell in column "R", then find the last cell in that row. Finally, select the range and autofill up to the row below the last row. Hopefully this explains what I want... Struggling with it already for hours...Please help.
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,327
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Not making much sense to me. Say the last used cell in column R is R100.

find the last cell in column "R", then find the last cell in that row. Finally, select the range
ok, say the last used column is column W that gives R100:W100. oK so far...

select the range and autofill up to the row below the last row.

What last row? the only last row you have mentioned is in R so you want it autofilled to R101:W101?

P.S. you should have bumped your other thread rather than start a new one.

http://www.mrexcel.com/forum/excel-questions/893182-visual-basic-applications-autofill-dynamic-range-error.html#post4306895]Re: vba: autofill dynamic range / error

Also a bit strange your link in the other thread to your cross-post isn't working now.
Can you please repost a link in this thread so we don't duplicate answers already given
 

Rotating_Potato

New Member
Joined
Oct 9, 2015
Messages
8
Hi Mark,

Sorry for creating a misunderstanding. Firstly, concerning the other thread: I didn't think about bumping the previous threat, and secondly, I have removed the thread from the other forum in relation to my issue. :)

In relation to the issue itself: when the last row in "R" column is found (say R100) and last column with data is found (say W100), the autofill should go up (R1:W1), and then move one row below (R2:W2). Hope that helps. So the idea is to autofill from R100:W100 to R2:W2. It can sound weird, but this is how it is...
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,327
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
First of all I feel sorry for the poster in the other forum who spent their time trying to help you if you just removed the thread.

Still lost what you mean by
autofill should go up (R1:W1), and then move one row below

but I will post the below and leave you to sort out the rest.... :coffee:
Code:
Sub lastr()
    Dim LstRw As Long, LstCo As Long, x As Range
    LstRw = Range("R" & Rows.Count).End(xlUp).row
    LstCo = Cells.Find("*", , xlValues, xlPart, xlByColumns, xlPrevious, False).Column
    Set x = Range(Cells(LstRw, "R"), Cells(LstRw, LstCo))
    x.AutoFill Range(x, Range(Cells(2, "R"), Cells(2, LstCo)))
End Sub
 

Rotating_Potato

New Member
Joined
Oct 9, 2015
Messages
8

ADVERTISEMENT

First of all I feel sorry for the poster in the other forum who spent their time trying to help you if you just removed the thread.

Still lost what you mean by

but I will post the below and leave you to sort out the rest.... :coffee:
Code:
Sub lastr()
    Dim LstRw As Long, LstCo As Long, x As Range
    LstRw = Range("R" & Rows.Count).End(xlUp).row
    LstCo = Cells.Find("*", , xlValues, xlPart, xlByColumns, xlPrevious, False).Column
    Set x = Range(Cells(LstRw, "R"), Cells(LstRw, LstCo))
    x.AutoFill Range(x, Range(Cells(2, "R"), Cells(2, LstCo)))
End Sub

Sir, thank you very much! Tomorrow I'm going to try your code and then will keep you posted on the result I received.
And the post was alive for a couple of hours, and after a few days it was silent - I removed it. Found this forum more effective compared to previous. And in a previous thread on this forum the gentleman were more concerned about my cross-post, which I sincerely accept as a mistake that won't repeat. :)
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,327
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Before posting back please have a read of the forum rules and guidelines (there are links to both in my signature block at the bottom of this post) it might save you some hassle. :)
 

Rotating_Potato

New Member
Joined
Oct 9, 2015
Messages
8

ADVERTISEMENT

Mark,
. I';
unfortunately, your code didn't work out... now sub generates "method Range of object _Global failed". I'm desperate, this last piece cannot let me finalize my project I worked on long hours and days
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,327
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Unfortunately you neglect to a) post what line is highlighted b) state any details of what the references are in the line that is highlights c) you don't state what does work and what doesn't.
 

Rotating_Potato

New Member
Joined
Oct 9, 2015
Messages
8
alright, here's what I got:

a) The line highlighted is x.AutoFill Range(x, Range(Cells(2, "R"), Cells(2, LstCo))). It produces Range of Object Global failed" error.
b) I don't know how to answer this question. Can you explain what u mean?
c) The code provided by you unfortunately doesn't even select the range...

In essence, what I'm trying to achieve is pretty straightforward. Here's an example:

1. Data is in A2:B4, headers are A1:B1
2. I want to autofill range with formula from last row (A4:B4) to top (A2:B2) without touching headers
3. I want to make the autofill dynamic, because users can insert rows / columns

How do I achieve this without hardcoding....? :(
 

ukmikeb

Well-known Member
Joined
Jul 10, 2009
Messages
2,757
Perhaps :-
Code:
Workbooks(file_main).Worksheets("sheet1").Activate
ActiveSheet.Range("B2").Select
LR = Range("B2").End(xlDown).Row 'determine last row
LC = Columns(Columns.Count).End(xlToLeft).Column ' determine last column
ActiveSheet.Range(Selection, Cells(Lr, LC)).Select
ActiveSheet.Range(Cells(Lr, 2), Cells(Lr, LC)).AutoFill Destination:=Selection

hth
 

Watch MrExcel Video

Forum statistics

Threads
1,109,085
Messages
5,526,762
Members
409,720
Latest member
Antwain

This Week's Hot Topics

Top