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.

Stock #	Description	Date in RR	Status
BZ101	2015 F150 White	Fri Sep 27/19 - 13:29	In Prog
Z93771	2016 Civic Grey	Fri Sep 27/19 - 13:49	In Prog
V9231	2017 Sierra	Fri Sep 27/19 - 08:23
V11151	2014 F150 Black	Fri Sep 27/19 - 11:56	In Prog
V10461	2014 Focus Blue	Fri Sep 27/19 - 09:43
V8401	2016 RAM White	Fri Sep 27/19 - 09:43
BZ162	2012 Sierra White	Fri Sep 27/19 - 11:56
Z93472	2007 Silverado Gray	Fri Sep 27/19 - 14:17
V10701	2011 Enclave Black	Fri Sep 27/19 - 14:17
V0641	2013 Silverado Black	Thu Sep 26/19 - 15:39	In Prog
V9811	2017 Sierra	Thu Sep 26/19 - 10:23
V10032	2001 Century	Thu Sep 26/19 - 09:32

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[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)