Counting rows in a column

[Sören_Marodören]

Hi,

I use the following line to count rows in a column A
Rows = Range("A" & Rows.Count).End(xlUp).Row
(I dont't know if this is the best way, but it works.)

Now I want to have a for loop that goes from column C to the last column
and count the number of rows in every column.

For Col = 3 to Last_Col
Rows = ....
Cells (1, Col) = Rows
Next

How shall the Row = ... be written to count the number of rows in column Col?
How can it be written to count columns in another worksheet?


Best regards,
/Sören Nilsson
Sweden

 

[Stefi]

One way:
RowsNo = Range(Left(Cells(1, Col).Address(False, False), 1 - (Col > 26)) &
Rows.Count).End(xlUp).Row

On Sheet2:
vRows = Worksheets("Sheet2").Range(Left(Cells(1, Col).Address(False, False),
1 - (Col > 26)) & Rows.Count).End(xlUp).Row

Don't use Rows as a variable name, it's a keyword!

Regards,
Stefi

„Sören_Marodören†ezt írta:

 

[Jacob Skaria]

Try this. This will give you the last column/row filled. If you are looking
for values then try with WorkSheetFunction.COUNTIF

For Col = 3 to ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column
Cells (1, Col) = ActiveSheet.Cells(Rows.Count, Col).End(xlUp).Row
Next

If this post helps click Yes

 

[Per Jessen]

Hello Sören

Try this:

Sub CountRows()
Dim TargetSh As Worksheet
Dim RowArray()

Set TargetSh = Worksheets("Sheet3")

LastCol = Range("A1").End(xlToRight).Column
ReDim RowArray(3 To LastCol)
For col = 3 To LastCol
RowArray(col) = TargetSh.Cells(Rows.Count, col).End(xlUp).Row
Next
End Sub

Regards,
Per

 

[Jacob Skaria]

I see 2 reasons.

1. Out of the below check where you have written your code.

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
'This Workbook event will be triggered for all sheets
End Sub

Private Sub Worksheet_Calculate()
'Each individual sheet has got its own Calculate event which will be
triggered only for that sheet
End Sub

2. Check the sheet reference used in your code. Check whether you have
specified ActiveSheet or Sheets(1) or ...

If this post helps click Yes

 

[Jacob Skaria]

Sorry the below is a wrong post....not related to the subject

If this post helps click Yes