How to determine the last row in a column

Question

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

Will the number of cells in each column change across the 260 columns or will it just change each time you run the code? — EdHunter
– EdHunter, Commented Dec 16, 2016 at 19:35
All you have to do is change lastRow = 6 so that the 6 is whatever your last row is. — EdHunter
– EdHunter, Commented Dec 17, 2016 at 15:58
Thanks Ed, I was trying to make it dynamic like @rdster suggested — Ricardo Barrios
– Ricardo Barrios, Commented Dec 20, 2016 at 20:51

Rdster · Accepted Answer · 2016-12-20 21:17:14Z

1

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.

edited Dec 20, 2016 at 21:17

answered Dec 16, 2016 at 19:38

Rdster

1,8722 gold badges19 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ricardo Barrios Over a year ago

Thank you, this is what I needed.

Scott Holtzman · Accepted Answer · 2016-12-16 20:49:30Z

0

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

answered Dec 16, 2016 at 20:49

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

2 Comments

Ricardo Barrios Over a year ago

For some reason this subtracted either 1 or 2 from the number of cells that should have been counted. It also put the cells in question a row below where I wanted it. Do you know why that may be?

Scott Holtzman Over a year ago

I'd have to see all your test data. I based my test on the small sample you gave. Placing the count 1 row below where you wanted can easily be fixed by changing lRow + 2 to lRow + 1.

Collectives™ on Stack Overflow

How to determine the last row in a column

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related