Counting Data and Rows Array 1

[nikohl]

Hi There,

I am fairly new to VB, so please bear with me. I am having trouble counting rows of Data in my worksheet. I don't want to know how many rows are in my selection, or how many rows are in my worksheet, but I want to know exactly the row number where my last data cell appears. Here's a quick example. I have 26 entries of data, but the last entry is in row 30. I would like to implement something that would return 30 to me.

I was also wondering if I can iterate through columns in a specific row. What I wanted to know was if I could do this:

Set rowArray = ActiveWorkbook.Worksheets(1).Rows
For each rowArray
'This will access the 5th column in the current row
rowArray(5).Value
Next

Any guidance would be greatly appreciated.

Thanks,
Nik

 

[SkipVought]

Hi,
There are several range related objects and properties that will be helpful.

1. CurrentRegion - When CurrentRegion is used on a range object, it defines the rows and columns in a table of data. So if you specify any cell or range within your table of data and modify it by CurrentRegion, you can extract from it the first row/column and the row/colum count AND do any number of other things to the table of data. Here's what the code would look like...

    Dim lRowFirst As Long, lRowCount As Long, lRowLast As Long
    Dim iColFirst As Integer, iColCount As Integer, iColLast As Integer
    
    With Cells(1, 1).CurrentRegion
        lRowFirst = .Row
        lRowCount = .Rows.Count
        iColFirst = .Column
        iColCount = .Columns.Count
    End With
    lRowLast = lRowFirst + lRowCount - 1
    iColLast = iColFirst + iColCount - 1

Hope this helps Skip,
metzgsk@voughtaircraft.com

 

[SkipVought]

Oh, by the way, a function that would return the last row could be written like this...

Function LastRow(rng As Range)
    Dim lRowFirst As Long, lRowCount As Long
    
    With rng.CurrentRegion
        lRowFirst = .Row
        lRowCount = .Rows.Count
    End With
    LastRow = lRowFirst + lRowCount - 1
End Function

Hope this helps Skip,
metzgsk@voughtaircraft.com

 

[nikohl]

Hi Skip,

I don't know if I understand the Range object correctly, but from what I understand it simply returns "cell or a range of cells". But if I know my range of cells, than I wouldn't have to create a function to find the last row there is data in. Also, if I put a few empty rows in between sets of data, I only get the number of cells in one set. Example: 15 rows of data followed by 3 empty rows followed by 11 rows of data. I run your second funtion with: Range("A1", "A65000&quot I get 15 returned to me.

Maybe we're looking at this the wrong way...maybe I should count backwards...is there a function to give me the coordinates of the cell that contains the last piece of data.

Thank You for your help Skip.

Nik.

 

[SkipVought]

nikohl,
Good table design does NOT permit completely empty rows.

But if you insist...

Function LastRow()
    Dim lMax As Long, lRow As Long
    lMax = 0
    For Each cell In ActiveSheet.UsedRange
        lRow = Cells(Cells.Rows.Count, cell.Column).End(xlUp).Row
        If lMax < lRow Then lMax = lRow
    Next
   LastRow = lMax
End Function

Skip,
metzgsk@voughtaircraft.com

 

[nikohl]

Hello Skip,

Your function works like a charm. I wish I could give you something in return, as I don't like to get anything without giving something back...if you ever have a Java/C++ question, let me know!

I also agree with you that good table construction should not have empty rows, but the data I am getting is being converted, so I really can't control that.

 

[SkipVought]

Hi,
Here at Tek-Tips the only reward that comes with a helpful or expert post is to click on the hyperlink in the lower left-hand corner of that contributor's post. This not only is a modest reward, but it also serves as a flag to others who might find a posting helpful.

Thanx for the kind response Skip,
metzgsk@voughtaircraft.com