Hello there,

Several of my worksheets contain a column (e.g., colum 5) of data with
varying length. Worksheet 1 may have 10 rows of data, worksheet 2 with 20
rows of data. The first two rows are occupied by the header on all worksheets.

How can I count the maximum of rows with data?

I encountered this code: Cells(Rows.Count, "a").End(xlDown).Row
but unfortunately it doesn't work properly (probably because I don't
understand what exactly the code is doing).

Thank you, I would be grateful for your help.

There is some useful code here here for finding the true used range in
Excel:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=82

From that:

Sub lrow()
Dim lastrow
lastrow = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues,
LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
MsgBox lastrow
End Sub



--
Steve

"FSPH" <(E-Mail Removed)> wrote in message
news:172B7524-F20B-401C-A5F4-(E-Mail Removed)...
> Hello there,
>
> Several of my worksheets contain a column (e.g., colum 5) of data with
> varying length. Worksheet 1 may have 10 rows of data, worksheet 2 with 20
> rows of data. The first two rows are occupied by the header on all
> worksheets.
>
> How can I count the maximum of rows with data?
>
> I encountered this code: Cells(Rows.Count, "a").End(xlDown).Row
> but unfortunately it doesn't work properly (probably because I don't
> understand what exactly the code is doing).
>
> Thank you, I would be grateful for your help.

Not sure how your code is set up, but the following statement with find the
row number of the last row that has data in it no matter what column that
data is in...

LastUsedRow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious).Row

Just change the ActiveSheet reference to whatever you are using to reference
the worksheet you are currently looking at. And I'm guessing, for your
purposes, you would want to subtract 2 from this number to get your count.

--
Rick (MVP - Excel)


"FSPH" <(E-Mail Removed)> wrote in message
news:172B7524-F20B-401C-A5F4-(E-Mail Removed)...
> Hello there,
>
> Several of my worksheets contain a column (e.g., colum 5) of data with
> varying length. Worksheet 1 may have 10 rows of data, worksheet 2 with 20
> rows of data. The first two rows are occupied by the header on all
> worksheets.
>
> How can I count the maximum of rows with data?
>
> I encountered this code: Cells(Rows.Count, "a").End(xlDown).Row
> but unfortunately it doesn't work properly (probably because I don't
> understand what exactly the code is doing).
>
> Thank you, I would be grateful for your help.

not sure what you want, but this will give you the row with the longest
column of data as long as the last column has a header in row 1.
this works on sheet1:

Sub test()
Dim lastcol As Long
Dim RealLastRow As Long
Dim arr As Variant
Dim i As Long
With Worksheets("sheet1")
lastcol = .Cells(1, Columns.Count).End(xlToLeft).Column
ReDim arr(1 To lastcol)
For i = 1 To lastcol
arr(i) = .Cells(Rows.Count, i).End(xlUp).Row
Next
RealLastRow = Application.Max(arr)
MsgBox RealLastRow
End With
End Sub

--

Gary Keramidas
Excel 2003


"FSPH" <(E-Mail Removed)> wrote in message
news:172B7524-F20B-401C-A5F4-(E-Mail Removed)...
> Hello there,
>
> Several of my worksheets contain a column (e.g., colum 5) of data with
> varying length. Worksheet 1 may have 10 rows of data, worksheet 2 with 20
> rows of data. The first two rows are occupied by the header on all
> worksheets.
>
> How can I count the maximum of rows with data?
>
> I encountered this code: Cells(Rows.Count, "a").End(xlDown).Row
> but unfortunately it doesn't work properly (probably because I don't
> understand what exactly the code is doing).
>
> Thank you, I would be grateful for your help.

See my posting for a shorter way to do this.

--
Rick (MVP - Excel)


"Gary Keramidas" <GKeramidasAtMSN.com> wrote in message
news:(E-Mail Removed)...
> not sure what you want, but this will give you the row with the longest
> column of data as long as the last column has a header in row 1.
> this works on sheet1:
>
> Sub test()
> Dim lastcol As Long
> Dim RealLastRow As Long
> Dim arr As Variant
> Dim i As Long
> With Worksheets("sheet1")
> lastcol = .Cells(1, Columns.Count).End(xlToLeft).Column
> ReDim arr(1 To lastcol)
> For i = 1 To lastcol
> arr(i) = .Cells(Rows.Count, i).End(xlUp).Row
> Next
> RealLastRow = Application.Max(arr)
> MsgBox RealLastRow
> End With
> End Sub
>
> --
>
> Gary Keramidas
> Excel 2003
>
>
> "FSPH" <(E-Mail Removed)> wrote in message
> news:172B7524-F20B-401C-A5F4-(E-Mail Removed)...
>> Hello there,
>>
>> Several of my worksheets contain a column (e.g., colum 5) of data with
>> varying length. Worksheet 1 may have 10 rows of data, worksheet 2 with 20
>> rows of data. The first two rows are occupied by the header on all
>> worksheets.
>>
>> How can I count the maximum of rows with data?
>>
>> I encountered this code: Cells(Rows.Count, "a").End(xlDown).Row
>> but unfortunately it doesn't work properly (probably because I don't
>> understand what exactly the code is doing).
>>
>> Thank you, I would be grateful for your help.
>

got it
--

Gary Keramidas
Excel 2003


"Rick Rothstein" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> See my posting for a shorter way to do this.
>
> --
> Rick (MVP - Excel)
>
>
> "Gary Keramidas" <GKeramidasAtMSN.com> wrote in message
> news:(E-Mail Removed)...
>> not sure what you want, but this will give you the row with the longest
>> column of data as long as the last column has a header in row 1.
>> this works on sheet1:
>>
>> Sub test()
>> Dim lastcol As Long
>> Dim RealLastRow As Long
>> Dim arr As Variant
>> Dim i As Long
>> With Worksheets("sheet1")
>> lastcol = .Cells(1, Columns.Count).End(xlToLeft).Column
>> ReDim arr(1 To lastcol)
>> For i = 1 To lastcol
>> arr(i) = .Cells(Rows.Count, i).End(xlUp).Row
>> Next
>> RealLastRow = Application.Max(arr)
>> MsgBox RealLastRow
>> End With
>> End Sub
>>
>> --
>>
>> Gary Keramidas
>> Excel 2003
>>
>>
>> "FSPH" <(E-Mail Removed)> wrote in message
>> news:172B7524-F20B-401C-A5F4-(E-Mail Removed)...
>>> Hello there,
>>>
>>> Several of my worksheets contain a column (e.g., colum 5) of data with
>>> varying length. Worksheet 1 may have 10 rows of data, worksheet 2 with
>>> 20
>>> rows of data. The first two rows are occupied by the header on all
>>> worksheets.
>>>
>>> How can I count the maximum of rows with data?
>>>
>>> I encountered this code: Cells(Rows.Count, "a").End(xlDown).Row
>>> but unfortunately it doesn't work properly (probably because I don't
>>> understand what exactly the code is doing).
>>>
>>> Thank you, I would be grateful for your help.
>>
>

Thanks smartin,
Your post helped me solve a question I posted today "end(xldown)


"smartin" wrote:

> FSPH wrote:
> > Hello there,
> >
> > Several of my worksheets contain a column (e.g., colum 5) of data with
> > varying length. Worksheet 1 may have 10 rows of data, worksheet 2 with 20
> > rows of data. The first two rows are occupied by the header on all worksheets.
> >
> > How can I count the maximum of rows with data?
> >
> > I encountered this code: Cells(Rows.Count, "a").End(xlDown).Row
> > but unfortunately it doesn't work properly (probably because I don't
> > understand what exactly the code is doing).
> >
> > Thank you, I would be grateful for your help.
>
> Maybe this:
>
> Function CountRows(ByVal MyRange As Range) As Long
> CountRows = Application.WorksheetFunction.CountA(MyRange)
> End Function
>

Hello there,

very useful code. However, how do I search for "*" in a particular column
(e.g., column 10)?

Thanks for your help.

FSPH


"Rick Rothstein" wrote:

> Not sure how your code is set up, but the following statement with find the
> row number of the last row that has data in it no matter what column that
> data is in...
>
> LastUsedRow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, _
> SearchDirection:=xlPrevious).Row
>
> Just change the ActiveSheet reference to whatever you are using to reference
> the worksheet you are currently looking at. And I'm guessing, for your
> purposes, you would want to subtract 2 from this number to get your count.
>
> --
> Rick (MVP - Excel)
>
>
> "FSPH" <(E-Mail Removed)> wrote in message
> news:172B7524-F20B-401C-A5F4-(E-Mail Removed)...
> > Hello there,
> >
> > Several of my worksheets contain a column (e.g., colum 5) of data with
> > varying length. Worksheet 1 may have 10 rows of data, worksheet 2 with 20
> > rows of data. The first two rows are occupied by the header on all
> > worksheets.
> >
> > How can I count the maximum of rows with data?
> >
> > I encountered this code: Cells(Rows.Count, "a").End(xlDown).Row
> > but unfortunately it doesn't work properly (probably because I don't
> > understand what exactly the code is doing).
> >
> > Thank you, I would be grateful for your help.
>
>

Well, if you want to search for the last character in a known column, I
would just use the "standard" look up from the bottom method...

LastRowInCol10 = Cells(Rows.Count, 10).End(xlUp).Row

--
Rick (MVP - Excel)


"FSPH" <(E-Mail Removed)> wrote in message
news:4F8D0E8D-8779-4562-BD1D-(E-Mail Removed)...
> Hello there,
>
> very useful code. However, how do I search for "*" in a particular column
> (e.g., column 10)?
>
> Thanks for your help.
>
> FSPH
>
>
> "Rick Rothstein" wrote:
>
>> Not sure how your code is set up, but the following statement with find
>> the
>> row number of the last row that has data in it no matter what column that
>> data is in...
>>
>> LastUsedRow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, _
>> SearchDirection:=xlPrevious).Row
>>
>> Just change the ActiveSheet reference to whatever you are using to
>> reference
>> the worksheet you are currently looking at. And I'm guessing, for your
>> purposes, you would want to subtract 2 from this number to get your
>> count.
>>
>> --
>> Rick (MVP - Excel)
>>
>>
>> "FSPH" <(E-Mail Removed)> wrote in message
>> news:172B7524-F20B-401C-A5F4-(E-Mail Removed)...
>> > Hello there,
>> >
>> > Several of my worksheets contain a column (e.g., colum 5) of data with
>> > varying length. Worksheet 1 may have 10 rows of data, worksheet 2 with
>> > 20
>> > rows of data. The first two rows are occupied by the header on all
>> > worksheets.
>> >
>> > How can I count the maximum of rows with data?
>> >
>> > I encountered this code: Cells(Rows.Count, "a").End(xlDown).Row
>> > but unfortunately it doesn't work properly (probably because I don't
>> > understand what exactly the code is doing).
>> >
>> > Thank you, I would be grateful for your help.
>>
>>

Hello Rick,

for some reason this code doesn't work.

I want to know the number of rows with data in them.

Example: In colum 10 the first 4 rows have data (i.e., something "written")
in them. Here are the data with heading "Data" in the first column:
Data
1
2
3

Given this, I expect LastRowInCol10 to be 4. However, I always get the
result 1. Any idea what I am doing wrong here?

Thank you for your continued help.

FSPH


"Rick Rothstein" wrote:

> Well, if you want to search for the last character in a known column, I
> would just use the "standard" look up from the bottom method...
>
> LastRowInCol10 = Cells(Rows.Count, 10).End(xlUp).Row
>
> --
> Rick (MVP - Excel)
>
>
> "FSPH" <(E-Mail Removed)> wrote in message
> news:4F8D0E8D-8779-4562-BD1D-(E-Mail Removed)...
> > Hello there,
> >
> > very useful code. However, how do I search for "*" in a particular column
> > (e.g., column 10)?
> >
> > Thanks for your help.
> >
> > FSPH
> >
> >
> > "Rick Rothstein" wrote:
> >
> >> Not sure how your code is set up, but the following statement with find
> >> the
> >> row number of the last row that has data in it no matter what column that
> >> data is in...
> >>
> >> LastUsedRow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, _
> >> SearchDirection:=xlPrevious).Row
> >>
> >> Just change the ActiveSheet reference to whatever you are using to
> >> reference
> >> the worksheet you are currently looking at. And I'm guessing, for your
> >> purposes, you would want to subtract 2 from this number to get your
> >> count.
> >>
> >> --
> >> Rick (MVP - Excel)
> >>
> >>
> >> "FSPH" <(E-Mail Removed)> wrote in message
> >> news:172B7524-F20B-401C-A5F4-(E-Mail Removed)...
> >> > Hello there,
> >> >
> >> > Several of my worksheets contain a column (e.g., colum 5) of data with
> >> > varying length. Worksheet 1 may have 10 rows of data, worksheet 2 with
> >> > 20
> >> > rows of data. The first two rows are occupied by the header on all
> >> > worksheets.
> >> >
> >> > How can I count the maximum of rows with data?
> >> >
> >> > I encountered this code: Cells(Rows.Count, "a").End(xlDown).Row
> >> > but unfortunately it doesn't work properly (probably because I don't
> >> > understand what exactly the code is doing).
> >> >
> >> > Thank you, I would be grateful for your help.
> >>
> >>
>
>