Automate Row Count

[dougmeece]

Good Morning Everyone,

Please bear with me; I am not really a programmer but I am trying to fix a problem with some Macros in Excel using VB code.

Situation: We have a customer that uses an Access Form to run billing and export the data to an Excel spreadsheet. That spreadsheet has 2 form buttons that kick off macros.

The first button transfers the data imported from Access into the main workbook sheet and then deletes the secondary worksheet. When doing this is should be counting the number of rows with data and populating column A with the correct record count. For some reason this is stopping at number 270 when there are a total of 604 records in this months file.

I am trying to code a loop in the macro code that will recognize whether column B has anything in it and then place the appropriate record count in column A. When it reaches the point of column B containing nothing then I want it to stop.

I have not been able to figure out how to do this. Just when I think I understand what I need to do something stops me. I apologize but I am not a programmer so this is a struggle for me to do. I thought I needed to declare something like intLoopNum as Integer RecNum As Integer and then colBdata as string. I then would set intLoopNum = to zero. I then wanted to has a Do While loop say

Do While colBdata is not null Then
RecNum = intLoopNum +1
Loop

I want to simply add the code to the end of my Sub which currently transfers the data from one worksheet to another and perform a sort. That part by the way works fine. After the sort is done I would like the count to occur.

My problem is I really have no idea how to actually write that. If someone can provide me with a simple way to do so it would be greatly appreciated.

Thank you, Doug

[si_the_geek]

Welcome to VBForums

Thread moved to 'Office Development/VBA' forum... note that while it certainly isn't made clear, the "VB Editor" in Office programs is actually VBA rather than VB, so the 'VB6' forum is not really apt

[Chrissy]

Is this what you are looking for? The only way this would not work is if there is a gap between rows. The count will stop if it comes to a blank row.

Sub CountRows()
Dim intLoopNum As Integer
 
'This assumes the data starts on row 1
intLoopNum = 1
 
'The intloopnum variable stands for the row number and the "2" stands for the column number.
Do While Trim(Cells(intLoopNum, 2)) <> ""
    intLoopNum = intLoopNum + 1
Loop
 
'Record number should be the result of intLoopNum
 
End Sub