Loop with equation for upper limit

Question

Does anyone know how to run a loop with a varying limit? Every time I need to run this macro my limit changes by CountA(Column A)-2. Is there a way to incorporate this into my loop?

Sub UsedR2()

With ActiveSheet
Range("A3").Select
Selection.End(xlDown).Select
ActiveCell.Offset(-1, 0).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
Range("A3").Activate
Dim p
For p = 1 To 46

Range("A3").Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
ActiveCell.Offset(-1, 0).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
Range("A3").Activate

Next p

Range("D3").Select
Selection.End(xlDown).Select
Selection.EntireRow.Delete
Range("D3").Select
Selection.End(xlDown).Select
Selection.Offset(1, -3).Select
Range(Selection, Selection.End(xlDown)).Delete

End With

End Sub

Answer 1

Try this:

'First, declare a variable to contain the info
Dim upBound as integer 'or "as Long" depending how many you expect    
upBound = Excel.WorksheetFunction.CountA(Range("A:A"))-2    
For p = 1 To upBound    
'then, the rest of your code

Note#1: Rang(A:A) is the range of the sheet your code is in. If you want to be more explicit you can write Worksheet("The_Name_of_the_Worksheet").Range("A:A") or Worksheet(Sheet#).Range("A:A"). To be even more precise and sure: Thisworkbook.Worksheet("The_Name_of_the_Worksheet").Range("A:A") or Thisworkbook.Worksheet(Sheet#).Range("A:A").

Note #2: read this for the use of the With statement.

I also suggest you to go and read this post on how to avoid using .Select for multiple good reasons.