0

I would like to find the last row in a range that I have selected so that my code is more dynamic and less likely to break if I exceed the range.

I am unsure of the syntax to use. Instead of P4201, I would like to select the last row with a value within column P, whatever that may be.

Selection.AutoFill Destination:=Range("P2:P4201")

I am just unsure of the last syntax to select the last row with a value with P. Instead of doing P10000, I would like to make it cleaner.

TylerH
  • 20,799
  • 66
  • 75
  • 101
Chase Dixon
  • 21
  • 1
  • Care to explain more about your current data layout and your end goal? Selecting a range more than often is not the best approach. You may benefit from reading [this](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) post on the matter. – JvdV Aug 16 '19 at 14:39
  • https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba/11169920#11169920 – Siddharth Rout Aug 16 '19 at 14:43
  • This spreadsheet will have new data each month with varying amounts. I would like to be able to have the macro select the last row instead of guessing how many over I should put as the end range. – Chase Dixon Aug 16 '19 at 14:45

1 Answers1

0

The below macro counts up to the last filled cell in column P, and selects the full range from P2 until P-end.

Sub Select_Range()
Dim I As String
I = Sheet1.Range("P" & Rows.Count).End(xlUp).Row 'Determine last filled cell and count rows
Sheet1.Range("P2" & ":P" & I).Select 'select full filled range in column P.
End Sub

Let me know if this is what you're looking for.

Plutian
  • 2,276
  • 3
  • 14
  • 23