Question regarding counting non-blank cells.

[rmacdear]

Good day all, this is my first post here.

I have a table of information that I edit each day, needing to have the newest information on the top of the list. I need to do a count of non-blank cells in column A, and as I append the list, I add a blank row at the top of the list. Well, I actually select row 3, insert a blank row above row 3, and copy a few formulas into the new row 3.

I have a formula, =COUNTA(OFFSET(A3,0,0,COUNTA($A:$A)-1,1)) that I've been trying to make work, but when I use a Macro to add the blank row, the cell reference noted in RED, changes to A4, A5 etc each time I add a row. I need this cell reference to ALWAYS be A3.

The Macro is pasted below. Thanks in advance for any suggestions or assistance. I'm lost. I've also posted a sample of the table in use as well.

Sub AppendRows()
'
' AppendRows Macro
' Add row to enable additional data to be entered.
'


'
Rows("3:3").Select
Selection.ListObject.ListRows.Add (2)
Range("D4").Select
Selection.AutoFill Destination:=Range("D3:D4"), Type:=xlFillDefault
Range("D3:D4").Select
Range("G4:I4").Select
Selection.AutoFill Destination:=Range("G3:I4"), Type:=xlFillDefault
Range("G3:I4").Select
Range("Q3").Select
Selection.ClearContents
Range("P3").Select
Selection.ClearContents
Range("r3").Select
Selection.ClearContents
Range("A2").Select
End Sub


Here is a sample of the table I am using.
[TABLE="width: 524"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Stock #[/TD]
[TD]Description[/TD]
[TD]Date in RR[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]

BZ101[/TD]
[TD]

2015 F150 White[/TD]
[TD]

Fri Sep 27/19 - 13:29[/TD]
[TD]

In Prog[/TD]
[/TR]
[TR]
[TD]Z93771[/TD]
[TD]2016 Civic Grey[/TD]
[TD]Fri Sep 27/19 - 13:49[/TD]
[TD]In Prog[/TD]
[/TR]
[TR]
[TD]V9231[/TD]
[TD]2017 Sierra[/TD]
[TD]Fri Sep 27/19 - 08:23[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]V11151[/TD]
[TD]2014 F150 Black[/TD]
[TD]Fri Sep 27/19 - 11:56[/TD]
[TD]In Prog[/TD]
[/TR]
[TR]
[TD]V10461[/TD]
[TD]2014 Focus Blue[/TD]
[TD]Fri Sep 27/19 - 09:43[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]V8401[/TD]
[TD]2016 RAM White[/TD]
[TD]Fri Sep 27/19 - 09:43[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BZ162[/TD]
[TD]2012 Sierra White[/TD]
[TD]Fri Sep 27/19 - 11:56[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Z93472[/TD]
[TD]2007 Silverado Gray[/TD]
[TD]Fri Sep 27/19 - 14:17[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]V10701[/TD]
[TD]2011 Enclave Black[/TD]
[TD]Fri Sep 27/19 - 14:17[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]V0641[/TD]
[TD]2013 Silverado Black[/TD]
[TD]Thu Sep 26/19 - 15:39[/TD]
[TD]In Prog[/TD]
[/TR]
[TR]
[TD]V9811[/TD]
[TD]2017 Sierra[/TD]
[TD]Thu Sep 26/19 - 10:23[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]V10032[/TD]
[TD]2001 Century[/TD]
[TD]Thu Sep 26/19 - 09:32[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[MARK858]

Have you tried changing the A3 to $A$3?

 

[rmacdear]

Have you tried changing the A3 to $A$3?

I have in fact tried this, and to my surprise, it changes the fixed address from $A$3 to $A$4. I don't know why this would happen.

 

[Peter_SSs]

Welcome to the MrExcel board!

Here is a sample of the table I am using.

There is nothing in that sample that looks like it could be the result of your formula.
Can you tell us exactly where your table is? That is what is the header row and what columns does it occupy altogether?
What cell/column are you trying to put that formula in?

BTW, for the future it helps your potential helpers if you indent your code and then use code tags in your post to preserve that indentation. My signature block below explains how. (It also has a link to help with posting s,mall screen shots)