我需要修改代码来计算列中可变数量的行 [英] I need to modify code to count a variable number of rows in a column

问题描述

我正在尝试找到一些excel vba代码，它对列中的所有单元格进行计数，该列具有高于活动单元格以上的值，不包括标题，并排除紧邻活动单元格上方的单元格。所以例如，如果我有一个列

I'm trying to find some excel vba code that counts all the cells in a column that have values above the active cell excluding the header and excluding the cell immediately above the active cell . So for example if I have a column that goes

1
5
4
N/A
4
current cell

我希望当前单元格等于2 （计数5和4，而不是N / AN / A，不是当前单元格以上的单元格，而不是第一个单元格）

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

列将不同。

我希望这可以连续260列。

I want this to happen for 260 consecutive columns.

我有以下代码< a href =https://stackoverflow.com/a/41159938/4053498>，但该列中的单元格数为6，而不是灵活：

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

推荐答案

如果您需要做是将lastRow动态更改为

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

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

更改A，任何列准确地为您提供最后一行。

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

这篇关于我需要修改代码来计算列中可变数量的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！