Getting total number of used rows
 

Hi

Is there any wya to find out how many rows are boing used in a sheet? I want
to write a macro which needs to operate only on the rows which have some data.

Is there similar way to get number of columns.

Thank You

Regards,
Pawan

Getting total number of used rows
 

hi
more that one way to do this.
1.
Dim s As Long
s = ActiveSheet.UsedRange.Rows
MsgBox s
2.
Dim r As Long
r = Cells(Rows.Count, "a").End(xlUp).Row
MsgBox r

others may post more.
Regards
FSt1
"Pawan" wrote:

Hi

Is there any wya to find out how many rows are boing used in a sheet? I want
to write a macro which needs to operate only on the rows which have some data.

Is there similar way to get number of columns.

Thank You

Regards,
Pawan

Getting total number of used rows
 

Sub dural()
nused = 0
Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1
For i = 1 To nLastRow
If Application.WorksheetFunction.CountA(Rows(i)) 0 Then
nused = nused + 1
End If
Next
MsgBox (nused)
End Sub

--
Gary''s Student - gsnu200801


"Pawan" wrote:

Hi

Is there any wya to find out how many rows are boing used in a sheet? I want
to write a macro which needs to operate only on the rows which have some data.

Is there similar way to get number of columns.

Thank You

Regards,
Pawan

Getting total number of used rows
 

Give this function a try...

Function MaxRowInUse(Optional WS As Worksheet, Optional _
FactorInHiddenRows As Boolean = False) As Long
Dim X As Long
Dim LastRow As Long
If WS Is Nothing Then Set WS = ActiveSheet
With WS
For X = 1 To .UsedRange.Columns.Count
If Not (Not FactorInHiddenRows And Columns(X).Hidden) Then
LastRow = .Cells(.Rows.Count, X).End(xlUp).Row
If LastRow MaxRowInUse Then MaxRowInUse = LastRow
End If
Next
End With
End Function

Note: If you don't specify a worksheet in the first (optional) argument,
then the active sheet is used. The second optional argument is the
interesting one... it lets you determine whether to include hidden rows or
columns when determining the maximum row and column that are in use; that
is, if a hidden row or column contains the maximum row or column, it will be
ignored unless the second argument is set to True. This allows you to get
the maximum row or column for what you see on the worksheet rather than for
what what any hidden data would return. I wasn't sure which would be the
most logical default for this second argument, so I chose not factor in
hidden rows or columns (that is, the functions return the maximum row and
column for only the visible data); if desired, this can be easily changed in
the declaration headers for each function.

Rick


"Pawan" wrote in message
...
Hi

Is there any wya to find out how many rows are boing used in a sheet? I
want
to write a macro which needs to operate only on the rows which have some
data.

Is there similar way to get number of columns.

Thank You

Regards,
Pawan

Getting total number of used rows
 

The reason my write up keeps saying "maximum row or column" is because it
was taken from a previous posting of mine where I posted the function's
companion function for finding the last column used on the worksheet. For
completeness sake, here is that companion function...

Function MaxColumnInUse(Optional WS As Worksheet, Optional _
FactorInHiddenColumns As Boolean = False) As Long
Dim X As Long
Dim LastColumn As Long
If WS Is Nothing Then Set WS = ActiveSheet
With WS
For X = 1 To .UsedRange.Rows.Count
If Not (Not FactorInHiddenColumns And Rows(X).Hidden) Then
LastColumn = .Cells(X, .Columns.Count).End(xlToLeft).Column
If LastColumn MaxColumnInUse Then MaxColumnInUse = LastColumn
End If
Next
End With
End Function

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
Give this function a try...

Function MaxRowInUse(Optional WS As Worksheet, Optional _
FactorInHiddenRows As Boolean = False) As Long
Dim X As Long
Dim LastRow As Long
If WS Is Nothing Then Set WS = ActiveSheet
With WS
For X = 1 To .UsedRange.Columns.Count
If Not (Not FactorInHiddenRows And Columns(X).Hidden) Then
LastRow = .Cells(.Rows.Count, X).End(xlUp).Row
If LastRow MaxRowInUse Then MaxRowInUse = LastRow
End If
Next
End With
End Function

Note: If you don't specify a worksheet in the first (optional) argument,
then the active sheet is used. The second optional argument is the
interesting one... it lets you determine whether to include hidden rows or
columns when determining the maximum row and column that are in use; that
is, if a hidden row or column contains the maximum row or column, it will
be ignored unless the second argument is set to True. This allows you to
get the maximum row or column for what you see on the worksheet rather
than for what what any hidden data would return. I wasn't sure which would
be the most logical default for this second argument, so I chose not
factor in hidden rows or columns (that is, the functions return the
maximum row and column for only the visible data); if desired, this can be
easily changed in the declaration headers for each function.

Rick


"Pawan" wrote in message
...
Hi

Is there any wya to find out how many rows are boing used in a sheet? I
want
to write a macro which needs to operate only on the rows which have some
data.

Is there similar way to get number of columns.

Thank You

Regards,
Pawan