0

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
Community
  • 1
  • 1
John
  • 1
  • 3

1 Answers1

0

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.

Community
  • 1
  • 1
simpLE MAn
  • 1,582
  • 13
  • 22
  • `upBound` will keep the value from `Excel.WorksheetFunction.CountA(Range("A:A"))-2`. Be sure the `Range("A:A)` you chose is on the right sheet. Then, I assumed you wanted the upper bound of the `For loop` to be `upBound` maybe it is `For p = 1 to 46 + upBound` you want, I'm not sure. – simpLE MAn Jun 08 '15 at 21:27