# COUNTIF Ignoring Hidden Rows

#### Jambo2000

##### New Member
Very New to Excel - Please go easy on me

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

#### jasonb75

##### Well-known Member
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.

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### Jambo2000

##### New Member
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

##### Well-known Member
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 :
VBA Code:
``````Sub Add_Row()
With Cells(Rows.Count, 1).End(3).EntireRow
.Copy .Offset(1)
.Offset(1).SpecialCells(xlCellTypeConstants).ClearContents
End With
End Sub``````

#### Jambo2000

##### New Member
Try this to add a row immediately below the last cell with data in column A :
VBA Code:
``````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

##### Well-known Member

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

#### footoo

##### Well-known Member
Perhaps this ? :
VBA Code:
``````Sub Rws()
Rows("6:16").Copy Cells(Rows.Count, 1).End(3)(3)
End Sub``````

#### Jambo2000

##### New Member

Perhaps this ? :
VBA Code:
``````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

#### Attachments

• Screen Grab.JPG
89.7 KB · Views: 3

#### footoo

##### Well-known Member
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

##### New Member
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

##### Well-known Member
What did you try - like this ? :
VBA Code:
``````Private Sub CommandButton1_Click()
Rows("6:16").Copy Cells(Rows.Count, 1).End(3)(3)
End Sub``````

Replies
9
Views
179
Replies
13
Views
386
Replies
7
Views
78
Replies
0
Views
38
Replies
1
Views
55