Autofilling in VBA through the last row

[jardenp]

I posted a similar question here VBA Autofill hanging - quick debug but I've found on that forum that once your question is buried you usually don't get a response. (Otherwise pretty good forum)

I would appreciate your help. In VBA macro code, when I want to extend a formula down through a column, I use something like this:

Range("H2").Select
        Selection.AutoFill Destination:=Range("H2:H" & Range("A2").End(xlDown).Row), Type:=xlFillDefault

This works fine if there is data on at least the next row (row 3 in this example). However, it gets buggy and strange when there is only data on the row the code is working on. That is, in this example, if there is only the header/column label row in row 1 and one row of data in row 2, then this code doesn't work well and seems to hang.

What is the best way to extend formulas down through however many rows there are and not run into problems if there is only one data row?

Thanks!

(I was brought here via a link in Rick Rothstein's signature on a post he gave a great answer too (much better than mine!))

[Rick Rothstein]

First off, welcome to the ExcelFox forum... and let me say I am honored that you found my postings of such quality that you followed me here... thank you. Now as to your question... instead of starting at the top of Column A and looking down, try starting at the very bottom of Column A and looking up... that will take you to the last filled cell in Column A. Your code, modified to do this would look like this...

Range("H2").AutoFill Destination:=Range("H2:H" & Cells(Rows.Count, "A").End(xlUp).Row), Type:=xlFillDefault

Note that I collapsed your code down to a single line... very rarely do you have to select a cell or range of cells in order to work with them. Whenever you see something like this...

Range(...).Select
Selection.SomeMethodOrProperty

try combining it to this...

Range(...).SomeMethodOrProperty

The reason... well, think about what the two line version is doing... it selects a range and does something to what was selected... but what is Selection... it is just a reference to what was selected, so you might as well work directly with the thing you were selecting and cutout the middle-porcess. By the way, doing that will speed up your code as well.

As an aside, and do not think for a moment I am trying to sway you from coming to this forum, because I am not, but whenever you find a message sinking down the list unanswered (either on MrExcel's forum where you came from or, for that matter, on this forum), then after a day or so, simply reply to your posting (the message is unimportant... most people just use the word "bump") and it will pop back up to the top of the list where it will hopefully be seen by someone with the background to answer it.

[jardenp]

Thank you so much for the response. I appreciate the tip about the redundancy of the .Select/Selection. pairing. I've learned most of my VBA coding by studying the output of the macro recorder, and separating the selection and acting on the selection is surely a result of that.

With your code I still run into problems if there is only data on rows 1 and 2. I have written a workaround using IF THEN, but I'd be interested to know if there is a way of sort of defining ranges "on the fly" that would allow for data only in rows 1 and 2, or even just in row one, since the data sets I'll be applying this code to will sometimes only have the header/label row and no data rows (for this particular sheet in the workbook).

Is the problem that the "Cells(Rows.Count, "A").End(xlUp).Row" part can't return the same row number as the "H2:H" part?

Thanks again.

-JP

[Excel Fox]

Try

Cells(Rows.Count, "H").End(xlUp).Row

instead

[Rick Rothstein]

Try

Cells(Rows.Count, "H").End(xlUp).Row

instead

I am not so sure of that. My understanding of the original question is that H1 contains a header and H2 contains a formula and the rest of Column H is empty and that jardenp wants to "copy down" the formula in H2 down to the bottom of his data and that he was using Column A to establish where that bottom of data was. His original problem, as I saw it, was that Column A's data was not contiguous and that the empty cells were interferring with the method he had chosen to figure out where the bottom of data in Column A was, so I gave him an alternative method of calculating where the last filled cell in Column A was. I will admit, though, that I do not fully understand the latest problem he is trying to describe to us.

[jardenp]

I need to use A as the reference, because H in this case is an added column, so it's cells won't be filled with anything. For all data rows, there will be data in column A.

When I run the code, I get "Run-time error '1004': Autofill method of Range class failed" and then when I click debug, it highlights the line with

Range("D2").AutoFill Destination:=Range("D2:D" & Cells(Rows.Count, "D").End(xlUp).Row), Type:=xlFillDefault

(This is from a testing example, but the "Cells(Rows.Count, "D").End(xlUp).Row" part is the same.

[jardenp]

I am not so sure of that. My understanding of the original question is that H1 contains a header and H2 contains a formula and the rest of Column H is empty and that jardenp wants to "copy down" the formula in H2 down to the bottom of his data and that he was using Column A to establish where that bottom of data was. His original problem, as I saw it, was that Column A's data was not contiguous and that the empty cells were interferring with the method he had chosen to figure out where the bottom of data in Column A was, so I gave him an alternative method of calculating where the last filled cell in Column A was. I will admit, though, that I do not fully understand the latest problem he is trying to describe to us.

Rick, you are correct that H:H will be empty. However, my problem isn't non-contiguous data in A:A, it's that sometimes I only have the header row or the header row and one data row. All data, if it's there, will be contiguous.

The problem seems to be that whether it's

Range(H2:H" & Range("A2").End(xlDown).Row

or

"H2:H" & Cells(Rows.Count, "A").End(xlUp).Row

It fails when there are no rows in A:A beyond row 2, the row in the original range selection (i.e., the "H2:H" part). Since the data I'll be using this code on may have 25 or 0 data rows, I'm wondering if there is an easy way to include all possibilities. Thanks for your help on this.

[Excel Fox]

Rick, my bad about the "H" vs "A" columns. Didn't pay attention.

For the problem above, it has to be the End(xlUp) method

So it should be

Range("H2:H" & Cells(Rows.Count, "A").End(xlUp).Row)