Autofill last row

[ai1094]

I have part of my VBA code below:

Range("W82:X88").Select
Selection.AutoFill Destination:=Range("W82:X5500")
Range("W82:X5500").Select


How can I make the selected range autofill to the last row? Data may contain more or less than 5,500 entries and I would like to make the code more dynamic.

 

[Peter_SSs]

How can I make the selected range autofill to the last row?

How do we determine the last row?
Is there a particular column that we could always use to determine that?

Also, can you tell us a bit about what is in W82:X88 and why you are using 7 rows to Autofill?

 

[ai1094]

Last row can be determined by Column A.

Also, I have formulas in W82 and X82, however the next 6 cells after Row 82 equal the cell above it.
For example:

Col W
=VLOOKUP Formula
=W82
=W83
=W84
=W85
=W86
=W87

The same formulas then apply for the next 7 rows. Column X follows the same logic. Is this a little more clear?

How do we determine the last row?
Is there a particular column that we could always use to determine that?

Also, can you tell us a bit about what is in W82:X88 and why you are using 7 rows to Autofill?

 

[Peter_SSs]

Does this do what you want?

Sub Fill_Em()
  Range("W82:X88").AutoFill Destination:=Range("W82:X" & Range("A" & Rows.Count).End(xlUp).Row)
End Sub

 

[ai1094]

This worked perfectly. Thanks!

Does this do what you want?

Sub Fill_Em()
  Range("W82:X88").AutoFill Destination:=Range("W82:X" & Range("A" & Rows.Count).End(xlUp).Row)
End Sub

 

[Peter_SSs]

This worked perfectly. Thanks!

You're welcome.