vba autofill / error range class

[Rotating_Potato]

Hello,

Desperately in the need of the advise of the forum's experts. I have a 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.

 

[MARK858]

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]

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]

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....

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]

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....

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]

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]

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]

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]

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]

Perhaps :-

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