How to use the count function in a macro

[PaBowSniper]

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:

Please Login or Register  to view this content.

Thanks for any help you may be able to provide!

[josnah]

try this...

Please Login or Register  to view this content.

Josnah

[PaBowSniper]

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!

try this...

Please Login or Register  to view this content.

Josnah

[PaBowSniper]

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,

[josnah]

hi there...

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

Please Login or Register  to view this content.

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...

Please Login or Register  to view this content.

tweak it like this:

Please Login or Register  to view this content.

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

[PaBowSniper]

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

[PaBowSniper]

I got it to work

THanks a bunch!

[PaBowSniper]

Now I had to make a few changes and was unable to do it straight VB b/c my knowledge isnt there. So I tried to recreate the Macro and then adjust the VB from what was said before however now its not working. Now the Count Result is = 2. I can not figure out what is up. The forumula is located in B8.

here is the new code:

Please Login or Register  to view this content.

I am now trying to do the COUNT again and it is not working. Any new advice?

thanks so much!

[josnah]

Replace:

Please Login or Register  to view this content.

With:

Please Login or Register  to view this content.

Question: are u still calculating from Cell A7? since you have moved the formula down another 4 rows?

Josnah

[PaBowSniper]

Hey thanks,

I did what you suggested and then the result was a 1. I then changed a7 to a11 b/c that was where the data began (i know it was difficult for you to see that with out seeing the whole spreadshee). Just trying to understand it i figured i would give it a shot. I think everything is good to go now. I appreciate your help. Thanks so much.