VBA - How to run code if only a certain number of rows are present

[jardenp]

I feel like this should be simple, but I don't know the language needed. In Excel 2010, I need VBA code to run according to the number of rows. Rows will always be grouped together at the top of the sheet, so there will be no blank rows in the middle of the data. Something like this:

create R
R = # of rows with with any populated fields

If R = 1, then run code1
If R = 2, then run code2
If R > 3, then run code3

(R will never = 0)

This problem arose from an AutoFill command running into trouble when there are not enough rows to satisfy a

[INDENT]Selection.AutoFill Destination:=Range("J3:L" & Range("A3").End(xlDown).Row)
[/INDENT]

type instruction.


Thanks!
PS. I see my user signature is out of date mentioning 2007 on XP. I'm running 2010 on Win 7.

 

[VoG]

Try like this

Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Select Case LR
    Case 1: Call code1
    Case 2: Call code2
    Case Else: Call code3
End Select

You can edit your signature

 

[JackDanIce]

Would this work?

Dim Row_Count as Long

Row_Count = Range("A" & Rows.Count).End(xlUp).Row

Select Case Row_Count
  Case Is = 1
     ' Your code for code1
  Case Is 2
     ' Your code for code2
  Case Is >= 3
     ' Your code for code3
  Case Else
     ' Your code for anything else
End Select

 

[jardenp]

Thanks for the quick reply.

Do I need to set up code1, code2, etc. as separate subs or something to call them? Or can I just insert the code for "code1", etc. in your example? Here's an example of one of the codes:

Range("J3").Select
        ActiveCell.FormulaR1C1 = "=IF(RC[-9]=R[-1]C[-9],RC[-7]+R[-1]C,RC[-7])"
Range("K3").Select
        ActiveCell.FormulaR1C1 = "=IF(RC[-10]=R[-1]C[-10],RC[-7]+R[-1]C,RC[-7])"

If I can keep it all in the same Sub, I'd prefer that.

Signature edited. Check.

 

[jardenp]

Thanks, JackDanIce. I think that's what I'm looking for. I'll test and get back.

Tanqueray and Jack Daniels - two of my favorites. Thanks guys.

 

[VoG]

That would be something like

Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Select Case LR
    Case 1: Range("J3").FormulaR1C1 = "=IF(RC[-9]=R[-1]C[-9],RC[-7]+R[-1]C,RC[-7])"
    Case 2: Range("K3").Formula = "=IF(RC[-10]=R[-1]C[-10],RC[-7]+R[-1]C,RC[-7])"
    Case Else: 'something else
End Select

 

[jardenp]

Thank you both very much. I'm off and running!