How to use the count function in a macro

What i am trying to do is count the number of rows that contain data.

It will always start in A7 and when creating the macro i hit control-shift-down arrow.

There will be a differnt number of rows for each data set. When i ran the macro it kept the result of the first data set that i used to create the macro.

here is the code:

Columns("B:L").Select
Range("B2").Activate
Columns("B:L").EntireColumn.AutoFit
Range("B4").Select
ActiveCell.FormulaR1C1 = "=COUNT(R[3]C[-1]:R[7199]C[-1])"

Thanks for any help you may be able to provide!

try this...

LastRowA = Range("A7").End(xlDown).Row - 4 '  -4 Cos' formula is in Row 4

Columns("B:L").EntireColumn.AutoFit
Range("B4").FormulaR1C1 = "=COUNT(R[3]C[-1]:R[" & LastRowA & "]C[-1])"

Josnah

That did it. I dont know if you can explain this to a NewB but if you can go for it. If i was not to update the VB how would I do that when recording the macro? If its too difficult to explain then dont worry bout it. At least it worked

thanks a bunch!

Originally Posted by josnah

try this...

LastRowA = Range("A7").End(xlDown).Row - 4 '  -4 Cos' formula is in Row 4

Columns("B:L").EntireColumn.AutoFit
Range("B4").FormulaR1C1 = "=COUNT(R[3]C[-1]:R[" & LastRowA & "]C[-1])"

Josnah

I just tweaked it a bit and now its good. My last request is how do i get the result to show up left justified in the cell?

thanks,

hi there...

u can't do this without updating the macro...

LastRowA = Range("A7").End(xlDown).Row - 4 '  -4 Cos' formula is in Row 4

the code above identifies the row no. as when you select Cell A7 & hit control-shift-down arrow in Excel. you have to less 4 rows from that cos your formula in cell "b4" is using referencing relative from row 4.
this will show the right no. each time your set changes.

Secondly, selecting cells is redundant. so i have removed all select codes. You will pick that up as you get along

lastly, if you need it left justified (aligned?)....
This is what will be generated if you use the macro recorder...

    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

tweak it like this:

    Range("A1").HorizontalAlignment = xlGeneral

hope i have been clear with the explanation....

Thanks for the info. I am sure you understood what I was asking for. I am going to look at your code and try to understand it. thanks a bunch