how to count populated rows?

How do I find out how many rows have data in them starting with row 4 and 
beyond? It's ok if I just know that column A has data in it.

I'm using VBA. I don't know how to use functions very well. But I know 
visual basic some. So if you could give me some VBA code to do this, that 
would be great. Thanks.
0
RyanCain (2)
8/11/2005 1:03:17 AM
excel.newusers 15348 articles. 2 followers. Follow

4 Replies
558 Views

Similar Articles

[PageSpeed] 0

Ryan,

I was doubtful that just knowing there was data would be enough...
So following is some code to cover several possibilities.
(you can understand how some of this works if you look up the terms 
used in the help file - just stick the cursor in a word and press F1)
'------------------
Sub FindStuff()
 Dim lngRw     As Long
 Dim lngCount  As Long
 Dim strBlanks As String
 Dim strData   As String

'Last row with data in Column A
 lngRw = Cells(Rows.Count, 1).End(xlUp).Row
 
'Count of cells with data in Column A (below cell A4)
 lngCount = WorksheetFunction.CountA(Range("A5", Cells(Rows.Count, 1)))
 
'Address of cells with blanks (below cell A4)
 strBlanks = Range("A5", _
             Cells(lngRw, 1)).SpecialCells(xlCellTypeBlanks).Address

'Address of cells with values (below cell A4)
 strData = Range("A5", _
          Cells(lngRw, 1)).SpecialCells(xlCellTypeConstants).Address
 
'Address of cells with formulas (below cell A4)
 strData = strData & "," & Range("A5", _
           Cells(lngRw, 1)).SpecialCells(xlCellTypeFormulas).Address
 
'Put it all together and display it
 MsgBox "Last row is " & lngRw & vbCr & "Location of blank cells is " & _
         strBlanks & vbCr & "Total cell count with data is " & _
         lngCount & vbCr & "Location of cells with data is " & strData
End Sub
'-------------------------------

Regards,
Jim Cone
San Francisco, USA



"Ryan Cain" <RyanCain@discussions.microsoft.com> wrote in message 
news:002F6E02-45CE-4E74-AF82-67DA460FFF8F@microsoft.com...
How do I find out how many rows have data in them starting with row 4 and 
beyond? It's ok if I just know that column A has data in it.

I'm using VBA. I don't know how to use functions very well. But I know 
visual basic some. So if you could give me some VBA code to do this, that 
would be great. Thanks.
0
jim.coneXXX (771)
8/11/2005 3:02:19 AM
to do it using a worksheet function try this:

=COUNTIF(A4:A100,">"""

--
Alex Delamai
-----------------------------------------------------------------------
Alex Delamain's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1127
View this thread: http://www.excelforum.com/showthread.php?threadid=39481

0
8/11/2005 7:58:49 AM
If your data are contiguous, COUNTA(A:A) will give you the result.  If 
not, Activesheet.Cells(ActiveSheet.rows.count,1).end(xlup).row will 
give you the last row with data.  Adjust the result for the offset for 
A4.

-- 
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <002F6E02-45CE-4E74-AF82-67DA460FFF8F@microsoft.com>, 
RyanCain@discussions.microsoft.com says...
> How do I find out how many rows have data in them starting with row 4 and 
> beyond? It's ok if I just know that column A has data in it.
> 
> I'm using VBA. I don't know how to use functions very well. But I know 
> visual basic some. So if you could give me some VBA code to do this, that 
> would be great. Thanks.
> 
0
8/12/2005 3:32:44 AM
My previous post was incorrect about the limitation on when COUNTA 
would be applicable.  It will work with contiguous *and* non-contiguous 
data.

-- 
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <002F6E02-45CE-4E74-AF82-67DA460FFF8F@microsoft.com>, 
RyanCain@discussions.microsoft.com says...
> How do I find out how many rows have data in them starting with row 4 and 
> beyond? It's ok if I just know that column A has data in it.
> 
> I'm using VBA. I don't know how to use functions very well. But I know 
> visual basic some. So if you could give me some VBA code to do this, that 
> would be great. Thanks.
> 
0
8/12/2005 3:35:15 AM
Reply:

Similar Artilces:

Adding a calculated ROW to a pivot table
Does anybody know how to add a calculated ROW to a pivot table? I have a pivot table that is returning totals at the bottom, as it should, but I *also* need it to return that total as a percentage of grand total, directly beneath the total. I've always done this free-form in the cells below a pivot table before, but the size of this pivot is dynamic so that's not an option. Also--I'm using the pivot in Access, not Excel directly. Anybody have any tips? Thanks! ...

Count File Names
Hi All, I having an issue with version control on Buisness cases. I currently have a spreadsheet that looks into a folder and picks up details from a number of files. However, they require version control numbers so there will eventually be multiple files say 2010-01 v1.0, 2010-01 v1.1. Is there a way I can: 1) Get a macro to count the number of 2010-01 files there are in the folder, and 2) Get my version control sheet to look at the latest version of the file (in this example: 2010-01 v1.1) Cheers, Chris See if this code works. I assume that 2010-01 is year and month. ...

Increasing # of rows in excel sheet
Hi all.. I usually import data from other programs such as SAP into excel and face the problem of excel running out of Rows.. is there a way that I can increase the total number of rows in my worksheet or any other solution possible to my problem. Thanks Rehan Hi no. 65536 rows is the maximum "Rehan" wrote: > Hi all.. > > I usually import data from other programs such as SAP > into excel and face the problem of excel running out of > Rows.. is there a way that I can increase the total > number of rows in my worksheet or any other solution > possibl...

Getting row indexes on Range
(I refer to C# code, but answers in VB are welcome) I have a Range in Excel, which includes several cells (the cells the user selected in the Excel sheet). The range might include the following cells A2, B7, G4. This means that the cells might not be connected. If I look at myRange.Cells.Count, it will return 3. If I look at myRange.Row, it will return 2 (if A2 was the first selected row by the user). Now, I need to get the row numbers of all selected rows, so in the above range, I need an int[] of {2, 7, 4}. But I can't see any solution to go through the Cells and get t...

Form creates unwanted rows in table
I've created an asset database which keeps track of computers, laptops etc. There is one company which has many departments and every department has many rooms. (surprise, surprise) There are four tables: tables Department and Asset and two "middle" tables DepartmentRoom and AssetRoom. The table structures are as follows: DEPARTMENT id name dep_type dep_num etc... ASSET id name a_type ip_num vendorID serial_num etc... DEPARTMENTROOM id name depID (foreign key to department) ASSETROOM id assetID (foreign key to asset) roomID (foreign key to depa...

Help with auto height of rows
Hello, I've got Excel 2002. Spreadsheet has over 3400 rows and 6 columns. No merged cells. All fonts are Arial size 10. The first 1809 rows are set to a height of '15.00 (20 pixels)' and the remaining rows are set to '12.75 (17 pixels)'. I've tried selecting all rows and then double clicking on a divider bar to get them all to adjust to auto height, but they stay at the heights listed. I want them all to be at the 12.75 (17 pixels) height when you do the auto height adjustment. Why won't this work on this spreadsheet? Thanks. Mark Nevermind - I fig...

Count ifs
Hi I have a list of data shown below, in 2 cloumns. I want to do a count if to show the total people in each department and location i.e. COUNT the number of occurances of people in HEAD OFFICE and PRODUCTION and then the number of occurances of people in HEAD OFFICE and FINANCE and so on.... Many thanks Location Department Head Office Production Birmingham Sales London Finance Head Office Finance Head Office Finance Head Office Finance Head Office Finance Head Office Finance Head Office Finance Head Office Finance Head Office Sales Head Office Sales Head Office...

Highlightin a row
Is there any way to automatically highlight a row with color in a large spreadsheet when you are entering data to know which row and cell you are in. *I need this hightlighting to move with the tab and enter key when I move to a different row/cell. Thanks -- Kim Hi try: http://www.cpearson.com/excel/RowLiner.htm Note: This kind of event procedure will disbable the UNDO functionality -- Regards Frank Kabel Frankfurt, Germany "Kim" <Kim@discussions.microsoft.com> schrieb im Newsbeitrag news:6DDFCD53-4341-4097-BCA1-F9E7831AF848@microsoft.com... > Is there any way to au...

Inserted rows, now need Counta function
Hi I've got a data dump. I've figured out how to insert a blank row after a change in name in column A and insert "Total" - so... bill.... bill.... bill Total - bob.... bob.... bob.... bob Total - What I need in column C next to total is to insert the COUNTA function for each person. Any ideas? cheers You say your code inserts a blank row after a change in name and inserts "Total", but your example doesn't show this. Can you post the code you are using to do your "insert"? It will probably be easier to handle the ...

Getting rid of blank rows withouth deleting them
Hi, i have a list that looks like this 22 43 22 41 ... I need the list to be together 22 43 22 41 ... However i can't use a macro or something to delete the entire ro because other worksheets are related to the cells, so when i delte th row, the #ref thing apears, somebody know a formula or something tha can help me? Thanks a lo -- sams ----------------------------------------------------------------------- samsg's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1624 View this thread: http://www.excelforum.com/showthread.php?threadid=27725 You could fi...

Count number of records after filtering
Hello, Probably a question that's easy to answer but I have been searching for hours now & it's driving me rather crazy: I have applied a filter to a form and now I want to know how many records I have got as a result, or better, I want to know if there are any records left - how do I do that? Tried so far: If Form.Recordset.Count = 0 Then ... If Iserror(Form.RecordSet.Count) Then ... If EOF(Recordset) Then ... but that all does not work. Thanks, Gerwin Gerwin: An easy way is to add a hidden text box, txtCount say, to the form, with a ControlSourc...

Populate customer through web services
I am trying to import a customer record through dynamics web services and have the following questions: 1. How do I get the customer class information to default for the customer. I am passing in the customer class and the class field populates on the customer but the class information does not default. There is a behaviour on the create customer but it is of type internal. If I change the behaviour on the policy, the customer does not import at all. 2. How do I get the address on the customer card to be imported. I can get the customer addresses to be imported into Great Plains but no...

Enter key will no longer return to begining of next row
From one worksheet to the next, the enter key will only activate the cell directly below the current cell. What Have I done to change the direction? Have checked the direction selection in Options and as suggested by MS Help made sure the Down was selected. Doesn't change a thing. Help! Sandy look in Tools | Options | and on the Edit tab check what's in "Move selection after Enter" Regards Trevor "Sandy" <anonymous@discussions.microsoft.com> wrote in message news:212b01c3e06b$ac7afe50$a401280a@phx.gbl... > From one worksheet to the next, the ent...

delete rows when any cells in column A is filled in RED color
Hi people, I need your help for a VB code that can delete the rows with any cells in column A being filled in RED color ? Alternatively, it will be fine for me if you have the code that can move the rows to the top when there are cells being filled in RED color in column A . Thanks in advance for help. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements If the colors are ...

Deleting a row when adding a new row -
I have a spreadsheet that contains 100 rows. I want to limit the number of rows to 100. I.e. if the user adds a row (i.e. row 101) then all data from row 1 is deleted. I do not have to worry about the deleted data as it is updated in the new row! Can anybody help. I think you're saying you want the first row to be automatically deleted when a row is added at the end of the table. The row will have to be deleted, it won't happen automatically. The macro language is there for such specific requirements. -- Regards from Virginia Beach, Earl Kiosterud www.smokeylake.com -------------...

Row highlight based on the 1-31 day of the month
I've got a table with dates displayed in the first column (ascending order). I would like to highlight each row based on the day of the month with one of 5 chosen colours (red, yellow, green, blue and orange). rows with the 1st of the month: red rows with the 2nd of the month: yellow ....... rows with the 6th of the month: red ....... Julian. You would use conditional formatting to display different colors based upon the day value. Your conditional formatting formula would look at the day value of the date. You will have multiple conditions for each row. Condition 1 Formula is: ...

How do I link cells, sheet to sheet, to recognize row deletions?
In Office XP - Excel - I want to copy a column from one sheet to another sheet in the same workbook. I want the copied links to recognize row deletions and insertions when I change the source sheet. When I use the Copy, Paste Special, Paste Links command, the linked sheet does not recognize a row deletion in the source sheet. Instead, the linked sheet displays a #Ref message in the cell related to the deleted row. One way to try .. Assume the source col to be linked is col A in Sheet1, A1 down In Sheet2 ------------- Put in any starting cell, say A1: =OFFSET(INDIRECT("Sheet1!A1&qu...

Counting number of worksheets in a Excel 2007
Dear Sir, I am using MS office 2007, and want to know what is command/formula to count the number of worksheets in a Excel. I deals with some taxation matter wherein I need to have hundreds of sheets in a file. Thank in advance. From within Excel code, Worksheets.Count will return the number of worksheets in the workbook. -- Rick (MVP - Excel) "Y D" <Y D@discussions.microsoft.com> wrote in message news:B2C3A4B9-22F4-4643-8850-BD340C4659C9@microsoft.com... > Dear Sir, > I am using MS office 2007, and want to know what is command/formula to >...

print multiple, separate rows on each page
How do I get Excel to print different rows on each page? I have table column heading I want to print on each page and a notes section as well, which is too large for a footer. In File, Page Setup, Sheet tab there are "Rows to repeat at top" and "columns to repeat at left". You should be able to select the rows you want, and they should appear on each page printed. (This is present in Excel 2003, anyways. I'm not sure about older versions). Kevin "DPixie" wrote: > How do I get Excel to print different rows on each page? I have table column > hea...

Turning a row color to "yellow" if one date field is greater than another.
I would like to automatically turn a row color to yellow if the current date is greater than a "needed by" date column. What is the best way to do this? Hi Select the rows necessary and then go to Format/Conditional Formatting. If your 'needed by' date is in column D use 'Formula Is' and then in the box type: =$D1=today() Hope this helps. Andy. "Ray Stevens" <nfr@nospam.com> wrote in message news:umTvfnURGHA.4976@TK2MSFTNGP11.phx.gbl... >I would like to automatically turn a row color to yellow if the current >date is greater than a &q...

Reading Last Row of Data
Is this possible? I have an excel document with two worksheets in it. Worksheet #2 is just a bunch of data sorted by date. Worksheet #1 displays data from the last row of worksheet #2. Is it possible to have excel detect the last row of data on Worksheet #2 and display it on Worksheet #1? Right now I am manually changing the cell references on worksheet #1 as I add data in worksheet #2. Any help would be greatly appreciated! -- sslack ------------------------------------------------------------------------ sslack's Profile: http://www.excelforum.com/member.php?action=getinfo&...

I want Combo box to be populated depending on customer category
When a customer number is entered on my form, the data populates the form, including the text box for the customer category. I want a combo box on that form to display only choices for that category. How do I modify the query that runs the row source for the combo box? Thanks, Sue On Thu, 7 Jan 2010 21:47:01 -0800, Sue R <SueR@discussions.microsoft.com> wrote: The RowSource should be something like: select * from Choices where Category = Forms!myForm!myCategoryTextbox -Tom. Microsoft Access MVP >When a customer number is entered on my form, the data po...

Format Rows
We have a shared spreadsheet. Someone changed all the row heights and other formatting options and I want to change them back. My question is (for Excel 2007) once you've done something on cell A1000000 does that increase the file size significantly? I can't just copy the populated cells in the worksheet to a new worksheet because I have many macro buttons set up and it would be too much trouble. Thanks for any help on this ...

Insert rows and paste without clearing constants
I need to Insert Rows and Paste the selected row a specified number of times per an input box value. I am using the following code as a start and have commented out the lines to clear the constants and that works fine except the autofill portion increments the constant values. Thanks in advance for any help. This is how I need the result to be: Room Part# 6 WN1B-24 6 WN1B-24 6 WN1B-24 6 WN1B-24 6 WN1B-24 6 WN1B-24 This is what I am getting: Room Part# 6 WN1B-24 7 WN1B-25 8 WN1B-26 9 WN1B-27 10 WN1B-28 11 WN1B-29 Sub CopyRows() ' Documented: http://www.mvps.org/dmcritchie...

Using VBA coding to count color Occurrences
Hi: I am using Office (Word, Excel, and Powerpoint) 2007, and Windows 7, and below is what I'm trying to accomplish, hopefully, with VBA coding: In Columns L & M I have dates that identify a beginning and end date for a class (L is "beginning" date and M is "end date") that a student has signed up for. If the student has not paid for their class, the class dates in Columns L & M are in "bold red font". Once payment has been made, the font changes from "bold red" to "unbold black" thanks to conditional formatting. ...