CODEWITHSUNDEEP
excelvba
Ricardo Barrios
Ricardo Barrios

Reputation: 29

How to determine the last row in a column

I'm trying to count all the cells in a column that have values above the active cell, excluding the first cell and the cell immediately above the active cell.

For example if I have a column

1
5
4
N/A
4
current cell

I want the current cell to equal 2. (Counting the 5 and 4, not the N/A, not the cell above current cell, and not the first cell)

The number of cells in the column will vary.

I want this for 260 consecutive columns.

I have the following code from this answer but the number of cells in the column is 6 rather than flexible:

Sub counter()

Dim firstCol as Integer
dim lastCol as Integer

firstCol = 1 'You can change this value depending on your first column
         ' for example you might use ActiveCell.Column

lastCol = firstCol + 260

Dim col As Integer
Dim lastrow As Integer
lastRow = 6  ' Make this the actual last row of the data to include

Dim cellcount As Integer

for col = firstCol to lastCol
  cellcount = 0
  For Each cell In ActiveSheet.Range(Cells(2, col), Cells(lastrow, col))
    If IsError(cell) Then GoTo skipcell

    If cell.Value > 0 And IsNumeric(cell) Then cellcount = cellcount + 1
skipcell:
  Next cell
  ActiveSheet.Cells(lastRow + 2, col) = cellcount
Next col

End Sub

Upvotes: 1

Views: 265

Answers (2)

Rdster
Rdster

Reputation: 1872

If all you need to do is make lastRow dynamic change it to 

lastRow = Cells(Rows.Count, "A").End(XlUp).Row

Change "A" to which ever column would accurately give you the last row.

Upvotes: 1

Scott Holtzman
Scott Holtzman

Reputation: 27269

Code below is tested (at least based on my understanding of what you are looking for).

Sub counter()

Dim ws As Worksheet
Set ws = Worksheets("mySheet") 'change as needed

Dim firstCol As Integer, lastCol As Integer
firstCol = 1
lastCol = 1 + 260

Dim i As Integer

For icol = firstCol To lastCol

    With ws

        Dim lRow As Long
        lRow = .Cells(.Rows.Count, icol).End(xlUp).Row

        .Cells(lRow + 2, icol).Value = Application.WorksheetFunction.Count(.Range(.Cells(2, icol), .Cells(lRow - 2, icol)))

    End With

Next

End Sub

Upvotes: 0

Related Questions