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

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:

> 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

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
---------------
Jacob Skaria


"Sören_Marodören" wrote:

> 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

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

"Sören_Marodören" <(E-Mail Removed)> skrev i
meddelelsen news:C46D7BB2-9563-4AEB-AA71-(E-Mail Removed)...
> 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

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


"Sören_Marodören" wrote:

> 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

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

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

> 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
>
>
> "Sören_Marodören" wrote:
>
> > 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