COUNTIF Ignoring Hidden Rows

[Jambo2000]

Very New to Excel - Please go easy on me

I'm putting together a spreadsheet to store and monitor TASKS
Each TASK will have smaller ACTIONS to go along with it. I have set the sheet so that once all the ACTIONS are 100% done, the TASK is also marked as 100% done.
I've created a tally at the top for both the total TASKS and the total ACTIONS.

Each TASK has been populated with 10 rows of ACTIONS.
As not every TASK will need all 10 ACTIONS, the rows not used will be hidden. Issue i have is the formula i'm using to count the total ACTIONS is : =COUNTIF(A7:A1000,"Action*")
This counts the total even if the row is hidden. Looking around on Google, you can't seem to have something that both ignores the hidden rows and uses the COUNTIF

Struggling to find a work around - Please help

 

[jasonb75]

Struggling to find a work around - Please help

A helper column is another way, far more efficient that arrays, especially with full columns. Assuming column B is empty (if not use any empty column).

In B7, fill down to end of data.
=SUBTOTAL(3,A7)

Then you can use =COUNTIFS(A:A,"*ACTION*",B:B,1) to get your count.

 

[Jambo2000]

To use full columns, just change the A1:A30 to A:A

Didn't work but the previous formula updates as i add additional rows so i can live with that.
Thank you again. Been driving me mad for days.

Now onto trying to work out ActiveX controls to copy & paste rows at the click of a button, with the formulas included......wish me luck

 

[footoo]

copy & paste rows at the click of a button, with the formulas included

Try this to add a row immediately below the last cell with data in column A :

Sub Add_Row()
With Cells(Rows.Count, 1).End(3).EntireRow
    .Copy .Offset(1)
    .Offset(1).SpecialCells(xlCellTypeConstants).ClearContents
End With
End Sub

 

[Jambo2000]

Try this to add a row immediately below the last cell with data in column A :

Sub Add_Row()
With Cells(Rows.Count, 1).End(3).EntireRow
    .Copy .Offset(1)
    .Offset(1).SpecialCells(xlCellTypeConstants).ClearContents
End With
End Sub

Footoo,

I was fooling around with it earlier to try and understand it. I used this:

Private Sub CommandButton1_Click()
Range("6:16").Copy Range("894:904")
End Sub

This did add in just what i wanted but as i need to be able to do this multiple times and miss a row each time, i need to do further work on it

 

[footoo]

You need to explain exactly what you are trying to do. Some sample data might help.

 

[footoo]

Perhaps this ? :

Sub Rws()
Rows("6:16").Copy Cells(Rows.Count, 1).End(3)(3)
End Sub

 

[Jambo2000]

Perhaps this ? :

Sub Rws()
Rows("6:16").Copy Cells(Rows.Count, 1).End(3)(3)
End Sub

So I've got 74 TASKS entered, each with 10 ACTIONS. As the ACTIONS are part or fully completed, it populates the percentage of the TASK completed. All of which is recorded in a tally at the top left. This all stays on screen at the top as you scroll down.
The Add TASK button currently when pressed, copies all the rows from TASK 1 and adds them at the bottom in specific rows. Using this code

Private Sub CommandButton1_Click()
Range("6:16").Copy Range("894:904")
End Sub

I'd like to be able to click this button multiple times and each time a new TASK is added at the bottom leaving 1 line between it and the previous TASK

Hope i have explained this ok, don't know the terms for things i'm sorry

 

[footoo]

Have you tried the macro?
Try it first then post what else it should do.
How do the data in C3:D4 get entered?

 

[Jambo2000]

Have you tried the macro?
Try it first then post what else it should do.
How do the data in C3:D4 get entered?

Tried the last code ytou suggested and this didn't do anything when i clicked the button. The current code, shown here:

Private Sub CommandButton1_Click()
Range("6:16").Copy Range("894:904")
End Sub

gets me the closest but is only a single use.

C3 and C4 are both populated with =COUNTIF formulas

 

[footoo]

What did you try - like this ? :

Private Sub CommandButton1_Click()
Rows("6:16").Copy Cells(Rows.Count, 1).End(3)(3)
End Sub