2

I'm constructing a program to automate some work, using record and then cleaning. It has worked well up until a calculation that takes too long for me. Its a simple nested IF statement 

ActiveCell.FormulaR1C1 = _
    "=IF(RC[-16]="""",""MISSING"",IF(RC[-14]="""",""MISSING"",RC[-14]-RC[-16]))"

We deal with data that can range from being only 10 rows up to a couple hundred thousand. My "solution" that I'm not happy with so far has limited the autofill to range A1:A35000 - which still takes Excel a bit to process. This was the solution to avoid xlDown taking me to the 1 millionth row. Further, I've tried reducing sheet size, that works as well but is not a good solution.

This is what the code looks like:

Selection.AutoFill Destination:=ActiveCell.Range("A1:A35000"), Type:= _
    xlFillDefault

What I want to do is to either:

  1. autofill from a range referenced by a number in a given cell (so if the data i put is 500 rows I have a cell I type in 500 and all the autofills go from A1:A500), or

  2. more preferably, this would be done automatically by having the program already recognize the range to autofill.

I've checked through the solutions and can't figure out how to apply it to my situation.

TylerH
  • 20,799
  • 66
  • 75
  • 101
Dm3k1
  • 187
  • 3
  • 8
  • 21

1 Answers1

2

I think you may be looking for the following ...

Dim ws as Worksheet
Set ws = Worksheets("Sheet1")

Dim usedRows as Long

'Option One (not recommended): USED RANGE METHOD 
usedRows = ws.UsedRange.Rows.Count

'Option Two (more robust) .END(xlUp) METHOD (Assuming you have your Data in column "RC")
usedRows = ws.Cells(ws.Rows.Count, "RC").End(xlUp).Row

'YOUR
'CODE
'HERE

Selection.AutoFill Destination:=ws.Range(Cells(1,1),Cells(usedRows,1)), Type:= _ xlFillDefault

if that column has the most used rows of any in your worksheet.

Thanks to @scott for pointing out the .End(xlUp) option's superiority :)

Katy
  • 261
  • 1
  • 6
  • 1
    Used Range can be somewhat untrustworthy. I'd prefer using `.End(xlup)` for a more robust solution. [Here](http://stackoverflow.com/questions/7423022/excel-getting-the-actual-usedrange) is a link to a similar discussion – scott Jan 04 '13 at 17:49
  • @scott Agreed! I like that especially because you can use it to isolate a single column, which appears to be what he is trying to do in this case. Answer edited accordingly. – Katy Jan 04 '13 at 18:11
  • 1
    At first look, that is exactly what im looking for! The only exception is that using Integer limits you to 32767 rows right? Would as LONG work better? – Dm3k1 Jan 04 '13 at 18:17
  • Ah, just saw the updated... will try this, I was running into the problem that it was going to the nth row. I'll update! – Dm3k1 Jan 04 '13 at 18:19
  • @user1949212 +1 for the pointing out the Long vs. Integer mistake I made. So edited. – Katy Jan 04 '13 at 18:23
  • Sorry, one more thing.. My autofill starts on the 3rd row. So using this method I notice that I am filling in this data 3 rows too far. This is probably a very simple fix that I should know. Solution? – Dm3k1 Jan 04 '13 at 18:44
  • @Dm3k1 try `Selection.AutoFill Destination:=ws.Range("A3:A" & usedrows), Type:= _ xlFillDefault` – scott Jan 04 '13 at 18:51
  • @Katy, you should change your destination to use the range string notation like my comment above so it stops at the last row, rather than going the number of rows from the activecell – scott Jan 04 '13 at 18:53
  • @Dm3k1 you simply need to change the index in first call of the Cells() function. 1,1 corresponds to A1; 1,2 corresponds to B1; 2,1 corresponds to A2; and so on. It looks like the desired combination you are looking for is 3,1 – Katy Jan 04 '13 at 20:53
  • Perfect, just did cells(usedrows - 3, 1) and it solved it. Thanks again – Dm3k1 Jan 04 '13 at 21:39