Question regarding counting non-blank cells.

rmacdear

New Member
Joined
Sep 28, 2019
Messages
2
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 #DescriptionDate in RRStatus


BZ101


2015 F150 White


Fri Sep 27/19 - 13:29


In Prog
Z937712016 Civic GreyFri Sep 27/19 - 13:49In Prog
V92312017 SierraFri Sep 27/19 - 08:23
V111512014 F150 BlackFri Sep 27/19 - 11:56In Prog
V104612014 Focus BlueFri Sep 27/19 - 09:43
V84012016 RAM WhiteFri Sep 27/19 - 09:43
BZ1622012 Sierra WhiteFri Sep 27/19 - 11:56
Z934722007 Silverado GrayFri Sep 27/19 - 14:17
V107012011 Enclave BlackFri Sep 27/19 - 14:17
V06412013 Silverado BlackThu Sep 26/19 - 15:39In Prog
V98112017 SierraThu Sep 26/19 - 10:23
V100322001 CenturyThu Sep 26/19 - 09:32

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

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,065
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Have you tried changing the A3 to $A$3?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,562
Office Version
  1. 365
Platform
  1. Windows
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,163
Messages
5,640,507
Members
417,148
Latest member
pe3087te

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top